CODEWITHSUNDEEP
excelvba
user25165324
user25165324

Reputation: 1

Excel VBA Macro - Updating Pivot

First of all, I am a complete novice with VBA, however I am trying to reuse code from a previous code to run a report. In short, the code, should open the file, refresh the connections, then refresh the pivots and save.

However I am struggling with the pivot bit. Please see below.

This bit is highlighted in yellow so assuming this is where the problem lies.

For Each Pivot In Sheet.PivotTables("PivotTable6").PivotCache.Refresh

Full code below

Sub Daily_Sales_Cognos() 'macro assigned toDaily Sales ButtonDim wb As WorkbookDim myPath As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

'Cav Daily Sales Cognos
With FilePicker
  .title = "Select OTCD Op Daily Margin - W:\Reports\YYYY\MM"
  .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1)
End With

'In Case of Cancel

NextCode:myPath = myPath
If myPath = "" Then Exit Sub

Set wb = Workbooks.Open(Filename:=myPath)

'_refreshes connections

ActiveWorkbook.RefreshAll

For Each Sheet In wb.WorksheetsFor Each Pivot In Sheet.PivotTables("PivotTable6").PivotCache.RefreshPivot.RefreshTablePivot.UpdateNextNext

Was expecting the pivot to update

Upvotes: 0

Views: 50

Answers (2)

Mai
Mai

Reputation: 27

Calculate
ActiveWorkbook.RefreshAll

worked for me.

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 7951

First, a general bit of advice: go into Settings, and turn "Require Explicit Variable Declaration" on. This will make your life a lot easier in future, because it will tell you if you type a variable name in wrong or something.

As for the code you have used: well, you seem to have squished about 7 lines into one, without any spaces… and not all of them were required anyway. Breaking it down into lines: (and adding 2 comments…)

For Each Sheet In wb.Worksheets
    For Each Pivot In Sheet.PivotTables
        ("PivotTable6").PivotCache.Refresh 'not necessary, and may even cause errors
        Pivot.RefreshTable
        Pivot.Update 'You could replace this and the previous line with Pivot.PivotCache.Refresh
    Next
Next

However, what you need to do is refresh the PivotCache, not the individual tables (multiple tables can use the same PivotCache) — so try something like this:

Dim pc As PivotCache 'Explicitly declare your variables!
For Each pc In wb.PivotCaches
    pc.Refresh
Next pc

Upvotes: 0

Related Questions