Use the same macro on multiple pivots

[tejal]

Hi all,

Is there a way for me to use the same code for multiple pivot tables (not all), these pivots are on the same sheet?

Sub PivotTableFieldValues1()

Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
Sheet5.Pivottables("PivotTable1").PivotCache.Refresh
Dim Pt As PivotTable
 Dim Pi As PivotItem
 
 Sheet5.Pivottables("PivotTable1").PivotFields("Date Range"). _
        ClearAllFilters
        
         Set Pt = Sheet5.Pivottables("PivotTable1")
         For Each Pi In Pt.PivotFields("Date Range").PivotItems
         
         On Error Resume Next
         
          If Pi.Name = Range("startdaycomp") Or Pi.Name = Range("finishdaycomp") Then
          Pi.Visible = True
          Else: Pi.Visible = False
        End If
   Next Pi
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Thank you.

Regards,
Tejal Sorathia

 

[AlphaFrog]

Instead of this...
Sheet5.Pivottables("PivotTable1")

This references the 1st pivot table on the active sheet.
ActiveSheet.Pivottables(1)

 

[tejal]

Hi, thank you for the reply. This is what the code looks like now.

Sub PivotTableFieldValues1()

Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
ActiveSheet.Pivottables(1).PivotCache.Refresh
Dim Pt As PivotTable
 Dim Pi As PivotItem
 
 ActiveSheet.Pivottables(1).PivotFields("Date Range"). _
        ClearAllFilters
        
         Set Pt = ActiveSheet.Pivottables(1)
         For Each Pi In Pt.PivotFields("Date Range").PivotItems
         
         On Error Resume Next
         
          If Pi.Name = Range("startdaycomp") Or Pi.Name = Range("finishdaycomp") Then
          Pi.Visible = True
          Else: Pi.Visible = False
        End If
   Next Pi
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

I get the following message: "unable to set the visible property of the pivotitem class".


Thank you.

 

[AlphaFrog]

Hi, thank you for the reply. This is what the code looks like now.

Sub PivotTableFieldValues1()

Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
ActiveSheet.Pivottables(1).PivotCache.Refresh
Dim Pt As PivotTable
 Dim Pi As PivotItem
 
 ActiveSheet.Pivottables(1).PivotFields("Date Range"). _
        ClearAllFilters
        
         Set Pt = ActiveSheet.Pivottables(1)
         For Each Pi In Pt.PivotFields("Date Range").PivotItems
         
         On Error Resume Next
         
          If Pi.Name = Range("startdaycomp") Or Pi.Name = Range("finishdaycomp") Then
          Pi.Visible = True
          Else: Pi.Visible = False
        End If
   Next Pi
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

I get the following message: "unable to set the visible property of the pivotitem class".


Thank you.

If all pivot items are set to .Visible = False, it will error. It requires at least one item remain visible.

You may want to first loop through all the pivot items and set them all visible. Then do a second loop to hide the ones that do not match the two ranges.

 

[tejal]

Hi, that error has gone. However I'm having no luck with getting this to work for multiple pivots. Is there a way I could upload a sample spread sheet? I'm a beginner with vba and am learning via examples so I don't think I've done it correctly.

Thank you again .

 

[AlphaFrog]

The forum doesn't allow to upload a file.

This example loops through pivot table indices 1, 3, and 5. Change the index numbers to suit.

[COLOR=darkblue]Sub[/COLOR] PivotTableFieldValues1()
    
    [COLOR=darkblue]Dim[/COLOR] i      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Pt     [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] Pi     [COLOR=darkblue]As[/COLOR] PivotItem
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Application.Calculation = xlCalculationManual
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] i [COLOR=darkblue]In[/COLOR] [COLOR=#ff0000]Array(1, 3, 5)[/COLOR]
    
        [COLOR=darkblue]Set[/COLOR] Pt = Sheet5.PivotTables(i)
        Pt.PivotCache.Refresh
        Pt.PivotFields("Date Range").ClearAllFilters
        
        [COLOR=green]'Show all items[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Pi [COLOR=darkblue]In[/COLOR] Pt.PivotFields("Date Range").PivotItems
            Pi.Visible = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]Next[/COLOR] Pi
        
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Pi [COLOR=darkblue]In[/COLOR] Pt.PivotFields("Date Range").PivotItems
            Pi.Visible = (Pi.Name = Range("startdaycomp") [COLOR=darkblue]Or[/COLOR] Pi.Name = Range("finishdaycomp"))
        [COLOR=darkblue]Next[/COLOR] Pi
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
        
    [COLOR=darkblue]Next[/COLOR] i
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[tejal]

Hi, Iv given the code a try. Unfortunately I still get the error message: "unable to set the visible property of the pivotitem class" at "Pi.Visible = True". I have some dates in the which aren't actually part of my data set as I group days and month. Could that be a reason?

 

[Ajit Nair]

Try the Below modified code

Sub PivotTableFieldValues1()
    Dim Pt As PivotTable
    Dim Pi As PivotItem
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    On Error Resume Next
    For Each Pt In Sheet5.PivotTables
        Pt.PivotCache.Refresh
        Pt.ClearAllFilters
        For Each Pi In Pt.PivotFields("Date Range").PivotItems
            If Pi.Name <> Range("startdaycomp") Or Pi.Name <> Range("finishdaycomp") Then
                Pi.Visible = False
            End If
        Next Pi
    Next Pt
    On Error GoTo 0
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Regards,
Ajit Nair

 

[Oeldere]

crosspost

Use the same macro on multiple pivots