Looping through pivot tables

Question

Hi,

I need to loop through about 12 worksheets and about 20 + pivot tables to get each pivot table to filter to a from week 2 to the week number chosen, once the week is chosen from a drop down list, . I am not sure if this can be done. But I need to find a something to work fast.

Someone posted the below suggestion before when I asked the question if I could create a macro to do this. I have no idea what to do to get this work. Could someone explain what to do, and how to do it. I am new to VB, and find this a bit over my head. I also need to have this done ASAP. Any ideas ? please included as much detail as possible. Please included as many steps as you can.

Thanks.

dim wks as worksheet

dim pvt as pivottable

for each wks in worksheets

  for each pvt in wks.pivottables

    'add pivot table code here to apply the filter

  next pvt

next wks

Answer 1

This should pick up the value from C2 in Sheet 1

Sub Test()

'

' Test Macro

'

Dim wks As Worksheet

Dim pvt As PivotTable

'On Error Resume Next

For Each wks In Worksheets

For Each pvt In wks.PivotTables

msgbox "About to modify " & pvt.name & " on sheet " & wks.name

pvt.PivotFields("TWeek").PivotItems(worksheets("Sheet1").Range("C2").Value).Visible = True

Next pvt

Next wks

End Sub

It is looking at all pivot tables on all sheets. The code aboe has a message box to confirm the pivot tables it is about to try to modify. If the pivot table does not have "TWeek" in it what did you want the code to do? There is nothing to set on that pivot table.

Answer 2

I posted

Sub Test()

'

' Test Macro

'

Dim wks As Worksheet

Dim pvt As PivotTable

On Error Resume Next

For Each wks In Worksheets

For Each pvt In wks.PivotTables

pvt.PivotFields("TWeek").CurrentPage = Sheets("Sheet1").Range("C2").Value

Next pvt

Next wks

End Sub

in a macro, when I ran it nothing happened. what am i missing ?

Answer 3

Yes it acts on ALL pivot tables on ALL worksheets.

There are 2 For/Next Loops. The outside loop traverses through all of the worksheets in the workbook. The inside loop traverses through each pivot table on the sheet specified in the outside loop.

Answer 4

Ok, sounds good so far, I don't have a strong understanding of vb, can you tell me if this is looping through all of the worksheets ?

This is what I have put in from your code, not sure if it will work.

Sub Test()

'

' Test Macro

'

  Dim wks As Worksheet

    Dim pvt As PivotTable

    On Error Resume Next

    For Each wks In Worksheets

        For Each pvt In wks.PivotTables

            pvt.PivotFields("TWeek").CurrentPage = Sheets("Sheet1").Range("C2").Value

        Next pvt

    Next wks

End Sub

Answer 5

Here is some code. It assumes you have a field called month that you have placed in the filter section of your pivot table. It sets that Month field to the value in cell A1 of Sheet1.

Sub test()

    Dim wks As Worksheet

    Dim pvt As PivotTable

    On Error Resume Next

    For Each wks In Worksheets

        For Each pvt In wks.PivotTables

            pvt.PivotFields("Month").CurrentPage = Sheets("Sheet1").Range("A1").Value

        Next pvt

    Next wks

End Sub