VB Script for Excel Refreshall Pivot Tables

[Dbllt02]

Hello,
I've run into some issues with my VB scripting. I'm a novice VB user so any help here would be welcomed. My code is below. Basically I open one excel workbook and have the VB script open, update, and close other workbooks. I cannot get the refreshall command to work correctly. The workbook has 10 sheets with multiple pivot tables on each sheet which pull data from an Access database. The refreshall updates all the pivot tables except the ones on the first, third, and fourth sheets, it's basically skipping those three sheets altogether.

Sub auto_open()

'Start code

Application.DisplayAlerts = False

Cells.Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.Previous.Select
Range("A1").Select

'Run_All_PivotTables()<~~~~~THis is where the problems begin

Application.Wait Now + TimeValue("00:00:5")

Application.Workbooks.Open ("X:\Planning & Analysis\Reporting\FlashReports\US\FlashReport_StoreLevel_Data_CTP_US.xlsm")

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:10:00")

ActiveWorkbook.Save

Application.quit

Application.Wait Now + TimeValue("00:00:5")

Application.Workbooks.Open ("X:\Planning & Analysis\Reporting\FlashReports\US\ACHClearance_Store.xlsm")

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeValue("00:10:00")

ActiveWorkbook.Save

Application.quit

End Sub

Any help is greatly appreciated!

Thanks

 

[Derek Brown]

Hello and welcome to The Board.
My understanding is that Refreshall in VBA will use the settings that appear in the 'external data' settings.
If it is set to 'background refresh' then you are allowing 10 minutes for that to take place - is that long enough? It is worth checking - if it is ticked, I would try unticking the 'background refresh' and then remove the 'wait'.