Pivot Table Page Field value from cell in another worksheet
in [General]
|
From: kernel on 23 May 2010 22:42 I have looked through the other posts on the subject and the combination of my low skills in VB and the other solutions not exactly covering my situation means I still need to ask for help. Scenario: Worksheet a = "Scorecard" FieldName = "CustomerNumber" Worksheet b = "Products Resume" PivotTable = "PivotTable2" PivotTable Page Field = "Account Number" When CustomerNumber value on worksheet "Scorecard" is changed I need the Pivot Table Page field "Account Number" to use this value to filter the pivot table. I have received some help for this problem and got this far - Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("CustomerNumber").Address Then Me.PivotTables(2).PivotFields("Account Number").CurrentPage = Range("CustomerNumber").Value Me.PivotTables(2).PivotCache.Refresh End If End Sub .....but I get the error "method 'PivotTables' of object '_Worksheet' failed Any ideas?
From: Roger Govier on 24 May 2010 04:47 Hi How many Pivot Tables do you have on worksheet b? If there is only 1, then change the code to PivotTables(1) in both instances within the code Alternatively, be specific and use PivotTables("PivotTable2") in both instances -- Regards Roger Govier kernel wrote: > I have looked through the other posts on the subject and the combination of > my low skills in VB and the other solutions not exactly covering my situation > means I still need to ask for help. > > Scenario: > > Worksheet a = "Scorecard" > FieldName = "CustomerNumber" > > Worksheet b = "Products Resume" > PivotTable = "PivotTable2" > PivotTable Page Field = "Account Number" > > When CustomerNumber value on worksheet "Scorecard" is changed I need the > Pivot Table Page field "Account Number" to use this value to filter the pivot > table. > > I have received some help for this problem and got this far - > > Private Sub Worksheet_Change(ByVal Target As Range) > If Target.Address = Range("CustomerNumber").Address Then > Me.PivotTables(2).PivotFields("Account Number").CurrentPage = > Range("CustomerNumber").Value > Me.PivotTables(2).PivotCache.Refresh > End If > End Sub > > ....but I get the error > > "method 'PivotTables' of object '_Worksheet' failed > > Any ideas?
From: kernel on 24 May 2010 18:30 Hi, Thanks for the response. I changed the code to - Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("CustomerNumber").Address Then Me.PivotTables("PivotTable2").PivotFields("Account Number").CurrentPage = Range("CustomerNumber").Value Me.PivotTables("PivotTable2").PivotCache.Refresh End If End Sub .....but I am still getting the "method 'PivotTables' of object '_Worksheet' failed" error. "Roger Govier" wrote: > Hi > > How many Pivot Tables do you have on worksheet b? > If there is only 1, then change the code to > PivotTables(1) > in both instances within the code > > Alternatively, be specific and use > PivotTables("PivotTable2") > in both instances > -- > Regards > Roger Govier > > kernel wrote: > > I have looked through the other posts on the subject and the combination of > > my low skills in VB and the other solutions not exactly covering my situation > > means I still need to ask for help. > > > > Scenario: > > > > Worksheet a = "Scorecard" > > FieldName = "CustomerNumber" > > > > Worksheet b = "Products Resume" > > PivotTable = "PivotTable2" > > PivotTable Page Field = "Account Number" > > > > When CustomerNumber value on worksheet "Scorecard" is changed I need the > > Pivot Table Page field "Account Number" to use this value to filter the pivot > > table. > > > > I have received some help for this problem and got this far - > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > If Target.Address = Range("CustomerNumber").Address Then > > Me.PivotTables(2).PivotFields("Account Number").CurrentPage = > > Range("CustomerNumber").Value > > Me.PivotTables(2).PivotCache.Refresh > > End If > > End Sub > > > > ....but I get the error > > > > "method 'PivotTables' of object '_Worksheet' failed > > > > Any ideas? > . >
From: Roger Govier on 26 May 2010 12:54 Hi If you want to mail me a copy of the workbook, I will take a look. Mail to roger at technology4u dot co dot uk Change the at to @ and dots to . and remove spaces, to make a valid eamil address -- Regards Roger Govier kernel wrote: > Hi, > > Thanks for the response. I changed the code to - > > Private Sub Worksheet_Change(ByVal Target As Range) > > If Target.Address = Range("CustomerNumber").Address Then > Me.PivotTables("PivotTable2").PivotFields("Account Number").CurrentPage > = Range("CustomerNumber").Value > Me.PivotTables("PivotTable2").PivotCache.Refresh > End If > End Sub > > ....but I am still getting the "method 'PivotTables' of object '_Worksheet' > failed" error. > > > > "Roger Govier" wrote: > >> Hi >> >> How many Pivot Tables do you have on worksheet b? >> If there is only 1, then change the code to >> PivotTables(1) >> in both instances within the code >> >> Alternatively, be specific and use >> PivotTables("PivotTable2") >> in both instances >> -- >> Regards >> Roger Govier >> >> kernel wrote: >>> I have looked through the other posts on the subject and the combination of >>> my low skills in VB and the other solutions not exactly covering my situation >>> means I still need to ask for help. >>> >>> Scenario: >>> >>> Worksheet a = "Scorecard" >>> FieldName = "CustomerNumber" >>> >>> Worksheet b = "Products Resume" >>> PivotTable = "PivotTable2" >>> PivotTable Page Field = "Account Number" >>> >>> When CustomerNumber value on worksheet "Scorecard" is changed I need the >>> Pivot Table Page field "Account Number" to use this value to filter the pivot >>> table. >>> >>> I have received some help for this problem and got this far - >>> >>> Private Sub Worksheet_Change(ByVal Target As Range) >>> If Target.Address = Range("CustomerNumber").Address Then >>> Me.PivotTables(2).PivotFields("Account Number").CurrentPage = >>> Range("CustomerNumber").Value >>> Me.PivotTables(2).PivotCache.Refresh >>> End If >>> End Sub >>> >>> ....but I get the error >>> >>> "method 'PivotTables' of object '_Worksheet' failed >>> >>> Any ideas? >> . >>
|
Pages: 1 Prev: Operating on Complex Numbers Next: Conditional Formatting Formula |