Vba

[Gary_H]

Hi All,

I have a filter in a pivot table in cell C3 on ten tabs. Each month I select all (new items added each month to the data set) and deselect the blanks.

I tried recording a macro but that wasn't helpful since new items are added to the data set each month.

Does anyone know advanced code that could automate this.

Thank you

 

[tonyyy]

Would clicking "Refresh All" in the Data tab accomplish what you want?

Cheers,

tonyyy

 

[tonyyy]

So, after thinking a bit more about your request, it seems the PivotTables are already refreshed with the new dataset, now it's a matter of making the added PivotItems visible (and hiding the "blanks").

You might give this a try...

Sub ShowAllData()

'   Sets all PivotItems to visible (except for blank)

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strName As String
Dim i As Long
Dim rngPivotField As Range
Dim pfOriginal As PivotField
Dim ws As Worksheet

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    ws(i).Activate
    Set pt = ActiveSheet.PivotTables(1)
    Set rngPivotField = Range("C3")
    Set pfOriginal = rngPivotField.PivotField
        For Each pi In pfOriginal.PivotItems
            strName = pi.Value
            If strName <> "(blank)" Then
                If pi.Visible = False Then pi.Visible = True
            End If
            If strName = "(blank)" And pi.Visible = True Then pi.Visible = False
        Next
Next

Application.ScreenUpdating = True
Exit Sub

This assumes there's only one PivotTable on each worksheet.

nb - Untried, untested, unproven.

Cheers,

tonyyy

 

[Gary_H]

Tony:

thank you for your reply.

I'm getting an error at:

ws(i).Activate (located in the middle of your code).

If by chance anything sticks out to you please let me know. Otherwise thank you for giving it a shot.

Best regards

 

[tonyyy]

Gary,

Please replace "ws(i).Activate" with "Worksheets(i).Activate"

Also, please replace "Exit Sub" with "End Sub"

Cheers,

tonyyy

 

[Gary_H]

Thanks. Hate to mention but I'm getting another error at:

Set pt = ActiveSheet.PivotTables(1) --> the next line

No worries - if anything comes to mind please let me know.

Thanks

 

[tonyyy]

Well, I guessed that "C3" is a PivotField, but it may well be a PageRange or some other type of filter. I'm sure you understand it's difficult to write code without a clear description of the underlying data structure.

Good luck!

tonyyy