pivots not refreshing - macro

[smakatura]

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

'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.

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

 

[shadow12345]

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.

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

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]

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....

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]

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]

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.

   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]

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]

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?

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

 

[Jerry Sullivan]

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...

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

'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.

 

[smakatura]

thanks that was the next thing I was going to look at