Help Modifying Worksheet_SelectionChange VBA

[amazzoli]

I am trying to set up a dashboard that uses getpivotdata and places into a set table form (client's request). What I would like to be able to do is use VBA to change the date filter on 3 pivot tables that are located on a different sheet than the dashboard. I need the client to be able to enter a date in a cell on the dashboard sheet and then have the VBA change the date filters on 3 pivot tables located on a separate sheet. I have tried to do this based on code from DedicatedExcel.com by updating the fields within the code, but I am getting an error on Field.CurrentPage = NewDate. I also need to figure how to change not one but three pivot tables (all will have same date filter). Thank you in advance for any tips!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewDate As String

'Here you amend to suit your data
Set pt = Worksheets("PivotTablesSheet").PivotTables("Ken_Offset")
Set Field = pt.PivotFields("Date")
NewCat = Worksheets("KenSummary").Range("B1").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters

Field.CurrentPage = NewDate
pt.RefreshTable
End With

End Sub

 

[NARAYANK991]

Hi ,

You are assigning the value to a variable named NewCat , in the following statement :

NewCat = Worksheets("KenSummary").Range("B1").Value

But then for the pivot table , you are using a variable named NewDate , in the following statement :

Field.CurrentPage = NewDate

Narayan

 

[amazzoli]

Thanks! I thought I had gotten all the NewCat and replaced them but I must have missed that one. Now the filters do change, but I am still getting the debug error at the bottom:

Field.CurrentPage = NewDate
pt.RefreshTable
End With

When the code fails and the debug dialog comes up, I click on debug and it highlights the comand:

Field.CurrentPage = NewDate

I think it might have something to do with the fact that my pivot tables are on one sheet and the cell I am basing the VBA change on is on a different worksheet.

 

[NARAYANK991]

Hi ,

It would help if you could upload your workbook.

Narayan

 

[amazzoli]

I will try to later on today thank you

 

[amazzoli]

Here is the workbook.

 

[NARAYANK991]

Hi ,

There was something wrong with your pivot table , since the date 9/12/2014 was present in the raw data , but the pivot table did not show it in the Report Filter dropdown.

I recreated the pivot table in a different part of the worksheet , and it works now.

Narayan

 

[amazzoli]

Thank you very much. I deleted the whole pivot sheet and just created all of my tables over again and then added in the VBA for the several different tables I needed!