Control Pivot Table from a cell in another worksheet

[Droolie520]

Hi I am trying to refresh a pivot table based on a cell in another worksheet. Can someone help me with the VBA coding? Waiting online, thanks in advance.

Controlling cell is in worksheet 1, cell B6

Pivot table is in worksheet 2, PivotTable4, filter is pay period.

Thank you!

 

[jorismoerings]

Hi,

take a look at this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$6" Then
        'Here you amend to suit your data
        Set pt = Worksheets(2).PivotTables("PivotTable1")
        Set Field = pt.PivotFields("Pay period")
        NewCat = Range("B6").Value
        
        'This updates and refreshes the PIVOT table
        With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
        End With
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Code must be placed on the worksheet code page of sheet1.
an example can be found here: https://app.box.com/s/u3vfw92yrj87jjtjivlqw539zsf48nbf

 

[Droolie520]

Thanks for the codes! However, when I pasted this to worksheet1, it gave me an error message: Invalid use of property. And when I ran debug, the first line is highlighted in yellow. Please help!

Hi,

take a look at this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$6" Then
        'Here you amend to suit your data
        Set pt = Worksheets(2).PivotTables("PivotTable1")
        Set Field = pt.PivotFields("Pay period")
        NewCat = Range("B6").Value
        
        'This updates and refreshes the PIVOT table
        With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
        End With
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Code must be placed on the worksheet code page of sheet1.
an example can be found here: https://app.box.com/s/u3vfw92yrj87jjtjivlqw539zsf48nbf

 

[jorismoerings]

Did you check my example and compare it to your workbook. Because without setup of your workbook I can't determine why my code runs in my sheet and doesn't in yours.

 

[Droolie520]

Yes. I downloaded your example and realized that you have codes for both "sheet1" and "this worksheet". I only copied and pasted what you gave me to "sheet1", could that be the problem?

 

[Droolie520]

So I compared your example to mind and modified my coding, see below. After the changes, cell B6 does control the pivot table.
However, it changes pivot table selection to "All", not the specific period I entered in cell B6.

Coding in Thisworksheet (General)

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$6" Then
        'Here you amend to suit your data
        Set pt = Worksheets("Pay Period").PivotTables("PivotTable4")
        Set Field = pt.PivotFields("Pay Period")
        NewCat = Range("B6").Value
        
        'This updates and refreshes the Pivot Table
        With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
        End With
    End If
    
    Application.EnableEvents = True
    
    Application.ScreenUpdating = True
    
    
End Sub

Coding in Sheet1 (Configuration)

Private Sub Worksheet_SheetChange(ByVal Target As Range)


'This line stops the worksheet from updating on every change, it only updtes when cell H6 or H7 is touched
If Intersect(Target, Sheets("Configuration").Range("B6")) Is Nothing Then Exit Sub


'Set the variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


'Here you amend to suit your data
Set pt = Worksheets("Pay Period").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Pay Period")
NewCat = Worksheets("Configuration").Range("B6").Value


'This updates and refreshes the Pivot Table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable


End With


End Sub

 

[jorismoerings]

So sorry, my mistake, the second code (you've coded on Sheet1 codepage) part must be removed and the the part you've coded on the ThisWorkbook page should be on the Sheet1 code page.

 

[Droolie520]

So sorry, my mistake, the second code (you've coded on Sheet1 codepage) part must be removed and the the part you've coded on the ThisWorkbook page should be on the Sheet1 code page.

Below is what I have in Sheet1 and nothing left in Thisworksheet (at lease nothing related to this matter). Change in cell B6 will select all options in pivot table4. I don't know what's wrong. I see your sample works and mine doesn't.

Private Sub Worksheet_Change(ByVal Target As Range)



    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$6" Then
        'Here you amend to suit your data
        Set pt = Worksheets("Pay Period").PivotTables("PivotTable4")
        Set Field = pt.PivotFields("Pay Period")
        NewCat = Range("B6").Value
        
        'This updates and refreshes the Pivot Table
        With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
        End With
    End If
    
    Application.EnableEvents = True
    
    Application.ScreenUpdating = True
    
    
End Sub