Call a macro by a change in a pivot table in a different sheet

[Kev Das]

Hi all

I want to run a macro when a pivot table is updated by a slicer. The macro will hide blank rows in Sheet1. This is the code I was using to do this when the pivot table was on Sheet 1.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.Run "'Sales Report.xlsm'!hideEmptyRows1"

End Sub

I have now had to move the pivot table to another sheet (Sheet3) and I am using slicers on Sheet1 (linked to the pivot table in Sheet 3) to update the data shown on the page.

I cannot figure out how to run the macro to hide the rows when the pivot table is not on the same sheet as the rows that need hiding.

Basically, I want the macro to run when I use a slicer to change the pivot table on another sheet.

Please could someone help with the code I need to do this?

Many thanks

Best Wishes

Kev
----------------------------------------------------------------------------
Mod Edit: Code Tags added

 

[Luke M]

Hi Kev,

The code you have above would need to be placed in the worksheet module for the sheet where the PivotTable has moved to. Additionally, you may need to amend your hideEmptyRows macro, if it isn't clear about which sheets to be looking at.

 

[Kev Das]

Hi Luke

Many thanks for your reply. Apologies but my VBA is not great. The code I am using for the hidden lines is:

Sub hideEmptyRows1()

Application.ScreenUpdating = False

For i = 40 To 66
  If ActiveSheet.Cells(i, 1) = "" Then
    ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
    Else: ActiveSheet.Cells(i, 1).EntireRow.Hidden = False

 
End If
Next i

Application.ScreenUpdating = True

End Sub

Please could you let me know how I can make this work specifically on Sheet3?

Many thanks

Kev

 

[Luke M]

Hi Kev.

I would suggest changing the row hiding macro to this:

Sub hideEmptyRows1(Optional wsName As String)
Dim ws As Worksheet
Dim I As Long

'Which worksheet are we working with?
If wsName = "" Then
    Set ws = ActiveSheet
Else
    Set ws = ThisWorkbook.Worksheets(wsName)
End If


Application.ScreenUpdating = False

For I = 40 To 66
    If ws.Cells(I, 1) = "" Then
        ws.Cells(I, 1).EntireRow.Hidden = True
    Else
        ws.Cells(I, 1).EntireRow.Hidden = False
    End If
Next I

Application.ScreenUpdating = True

End Sub

and then the code in the sheet module to this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call hideEmptyRows1(Target.Parent.Name)

End Sub

 

[Kev Das]

Many thanks Luke!

All the best

Kev