ODB connection refresh to dozens on pivot tables on seperate sheets - SLOW

[dversloot1]

Hello,

I've got a workbook with dozens of pivot tables connected to an ODB connection that are used to summarize many things. As a new month is loaded into the database, I will go into the file and update the pivots. I've automated the date change in the pivot, however, the file is taking upwards of an hour to refresh. I understand that refreshing one pivot table per sheet should refresh all others sharing the same connection, but it is taking ages to update. Is there a simpler, faster, way to do this?

Private Sub CommandButton1_Click()

Sheets("Sheet1").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Dist Raw1").Visible = True
Sheets("Large Base Pivots").Visible = True

Dim yr, qtr, mth As Long

yr = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Dist Raw1").Range("AQ3:AU26"), 5, False)
qtr = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Dist Raw1").Range("AQ3:AU26"), 3, False)
mth = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("Dist Raw1").Range("AQ3:AU26"), 4, False)

'''''''Sheet1''''''
Sheets("Sheet1").Select

Dim PTNos1 As Variant
Dim i1 As Long
Dim pt1 As PivotTable
Dim Field1 As PivotField
Dim NewCat1 As String
PTNos1 = Array(8, 9, 10)
Range("D1").Value = 0
For i1 = LBound(PTNos1) To UBound(PTNos1)
Set pt1 = ActiveSheet.PivotTables("PivotTable" & PTNos1(i1))
Set Field1 = pt1.PivotFields("[Time].[Fiscal Date].[Fiscal Year]")
NewCat1 = "[Time].[Fiscal Date].[Fiscal Year].&[" & yr & "].&[" & qtr & "].&[" & mth & "]"
With pt1
Field1.CurrentPageName = NewCat1
End With
Range("D1").Value = Range("D1").Value + 1
UserForm1.lbl1.Caption = Range("D1").Value
Next i1

''''''Sheet3''''''
Sheets("Sheet3").Select

Dim PTNos2 As Variant
Dim i2 As Long
Dim pt2 As PivotTable
Dim Field2 As PivotField
PTNos2 = Array(1)
Range("D1").Value = 0
For i2 = LBound(PTNos2) To UBound(PTNos2)
Set pt2 = ActiveSheet.PivotTables("PivotTable" & PTNos2(i2))
Set Field2 = pt2.PivotFields("[Time].[Fiscal Date].[Fiscal Year]")
With pt2
Field2.CurrentPageName = NewCat1
End With
Range("D1").Value = Range("D1").Value + 1
UserForm1.lbl2.Caption = Range("D1").Value
Next i2

'''''''DIST RAW1 SHEET'''''''
Sheets("Dist Raw1").Select

Dim PTNos3 As Variant
Dim i3 As Long
Dim pt As PivotTable
Dim Field3 As PivotField
PTNos3 = Array(3, 4)
Range("D1").Value = 0
For i3 = LBound(PTNos3) To UBound(PTNos3)
Set pt3 = ActiveSheet.PivotTables("PivotTable" & PTNos3(i3))
Set Field3 = pt3.PivotFields("[Time].[Fiscal Date].[Fiscal Year]")
With pt3
Field3.CurrentPageName = NewCat1
End With
Range("D1").Value = Range("D1").Value + 1
UserForm1.lbl3.Caption = Range("D1").Value
Next i3

'''''''LARGE BASE PIVOTS'''''''
Sheets("Large Base Pivots").Select

Dim PTNos As Variant
Dim i As Long
Dim pt4 As PivotTable
Dim Field4 As PivotField
PTNos = Array(15, 5, 2, 10, 11, 12, 13, 6, 23, 22, 21, 20, 19, 9)
Range("D1").Value = 0
For i = LBound(PTNos) To UBound(PTNos)
Set pt4 = ActiveSheet.PivotTables("PivotTable" & PTNos(i))
Set Field4 = pt4.PivotFields("[Time].[Fiscal Date].[Fiscal Year]")
With pt4
Field4.CurrentPageName = NewCat1
End With
Range("D1").Value = Range("D1").Value + 1
UserForm1.lbl4.Caption = Range("D1").Value
Next i

If CheckBox1.Value = True Then
Call pivotrefresh
End If

MsgBox ("Data has been updated for " & ComboBox1.Value)

UserForm1.Hide

End Sub


Sub pivotrefresh()

Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

Sheets("Sheet3").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

Sheets("Dist Raw1").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh

Sheets("Large Base Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

'Non ODB pivots
Sheets("Regional Overview").Select
ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable12").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable13").PivotCache.Refresh

Sheets("Distributor Overview").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh

Sheets("Top 50 Skus").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

Sheets("Top 50 NEW SKUs").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

End Sub

 

[dversloot1]

The pivotrefresh macro is refreshing one pivot table per sheet because many other pivots on that sheet share the same connection.