pivots not refreshing - macro

smakatura

Board Regular
Joined
May 27, 2011
Messages
141
I had a macro that was working fine, I tried to add one table and it broke. I then manually recorded a new macro of all the actions (thinking that would give me the proper code), but the code will still not work.

the rerecorded code is
HTML:
'refresh main pivot table    
    Sheets("Executive").Select
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Cells.Select
    Selection.Copy
 
'paste main table onto other worksheets within the workbook
    Sheets("057").Select
    ActiveSheet.Paste

    Sheets("070").Select
    ActiveSheet.Paste

    Sheets("133").Select
    ActiveSheet.Paste

    Sheets("Island").Select
    ActiveSheet.Paste
    
'filter the other worksheets to show just the desired data
    Sheets("057").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields
        ("Campus").CurrentPage = "school 057"

    Sheets("070").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields
        ("Campus").CurrentPage = "school 070"

    Sheets("133").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields
         ("Campus").CurrentPage = "school 133"

    Sheets("Island").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields
          ("Campus").CurrentPage = "school 099"
End Sub

it refreshes the main pivot table fine.
It pastes the datar in fine.
it will not put the right data in the other tables.
HTML:
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Campus").CurrentPage = "school 057"

I had just the 3 numberd tabs and it was working fine, I added the "Island" tab and of course did not keep my old stuff and now it will not even do the older tabs.

help
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
Im not sure if this would help you... but the below will refresh every pivot table you have. If thats not what you want, im not sure if selecting a cell in the sheets would help.

Could you maybe tell me what you are trying to do if not and maybe I (or more likely someone better at it than me) could help.

Code:
Sub AllWorksheetPivots()

Dim pt As PivotTable



    For Each pt In ActiveSheet.PivotTables

        pt.RefreshTable

    Next pt

    

End Sub


Or Refresh Chosen Pivot Tables in a Worksheet
Code:
Sub ChosenPivots()

Dim pt As PivotTable



    For Each pt In ActiveSheet.PivotTables

    

        Select Case pt.Name

            Case "PivotTable1", "PivotTable4", "PivotTable8"

                pt.RefreshTable

            Case Else

        End Select

    Next pt

    

End Sub
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
smakatura,

If your PivotTable PageField Filter has the "Select Multiple Items" option checked, then you'll need to clear the filters before trying to set a CurrentPage.

You could do this....
Code:
With Sheets("057").PivotTables("PivotTable1").PivotFields("Campus")
   .ClearAllFilters
   .CurrentPage = "school 057"
End With

...or even better clear the filter from you master PivotTable before copying it.

Have you considered Copying the entire Sheet and renaming it instead of Copy-Pasting all the Cells?
 

smakatura

Board Regular
Joined
May 27, 2011
Messages
141
refreshing the pivot table is not really the issue. I need it to go from all campues to just one of the 4 options.

the campus field is already being cleared of all filters and does not ahve multipe options 'selected'.

I tried the .current page = school 057 from above but I think the part that is causing the issue is the "PivotTable1" part. but not sure.

so still notworking.

that is why this is so strange, I even let the document record the macro to perform the functions and those recorded actions don't work.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787

ADVERTISEMENT

I tried the .current page = school 057 from above but I think the part that is causing the issue is the "PivotTable1" part. but not sure.

so still notworking.

that is why this is so strange, I even let the document record the macro to perform the functions and those recorded actions don't work.

The macro recorder can be a great tool; however sometimes what gets recorded won't work when replayed because the environment has changed from when the macro was recorded.

In your case, you recorded the macro while copy-pasting the cells on your PivotTable sheet 3 times. Excel auto-numbered the new PivotTables
PivotTable1, PivotTable2 and PivotTable3.

If you deleted these PivotTables and then tried to run your macro again, Excel would auto-number these PivotTable4, PivotTable5 and PivotTable6.
That's okay for the first part of your macro, but the second part that sets the filters won't work. So, I believe you are correct that the PivotTable names are the problem.

If you only have one PivotTable on each sheet, one way to handle this would be to reference the PivotTable's Item number instead of its name.

Code:
   Sheets("057").Select
    ActiveSheet.PivotTables(1).PivotFields
        ("Campus").CurrentPage = "school 057"

    Sheets("070").Select
    ActiveSheet.PivotTables(1).PivotFields
        ("Campus").CurrentPage = "school 070"

I'd still encourage you to look into copying the entire sheets instead of pasting the cells. ;)

Hope this helps!
 

smakatura

Board Regular
Joined
May 27, 2011
Messages
141
Jerry,

that is great. I love the different way of looking at the pivot table "refreshing" to the right campus.

I will change them to be the copying of the tab, I never thought of that as an option. but I can definetly see how it would save time and keep the memory from being used faster.

thank you for the tips
 

smakatura

Board Regular
Joined
May 27, 2011
Messages
141

ADVERTISEMENT

Re: pivots not refreshing/sort by field - macro

I have one other part to add to this question. if I want to have it look up "school 099" and "school 022" instead of just the one in the follwoing code, what would be the proper syntax?

HTML:
Sheets("Island").Select
    On Error Resume Next
    ActiveSheet.PivotTables(1).PivotFields("Campus").CurrentPage = "school 099"
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Re: pivots not refreshing/sort by field - macro

I have one other part to add to this question. if I want to have it look up "school 099" and "school 022" instead of just the one in the follwoing code, what would be the proper syntax?

If you add the function Filter_PivotField shown below to your standard module, then the syntax would be...

Code:
    Filter_PivotField _
        pvtField:=Sheets("Island").PivotTables(1).PivotFields("Campus"), _
        varItemList:=Array("school 099","school 022")

Code:
'This function can be called to filter a single PivotField
'to show only the items that are listed in an Item List.

'The function is called using two required parameters:
'    pvtField: The PivotField to be filtered
'    varItemList: A Variant Array of the items to be Visible

'The varItemList parameter can be either an array or a range.

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String, blTmp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
         
    If Not (IsArray(varItemList)) Then
         varItemList = Array(varItemList)
    End If
 
    With pvtField
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "None of filter list items found."
            Exit Function
        End If
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Function

This function also works with Rowfields and has some error checking. The task you describe could be done with less code; however putting this in your module gives you more options if your layout changes in the future.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,896
Messages
5,621,497
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top