Vba pivot tables

Question

how r u? Hope you can help me in this..... I have 2 files, A and B. I have the data in A, and the respective pivot tables in B but opening B with the "Pivot Tables Refresh" becomes very very slow because of the links. How can i do it in VBA so opening B doesnt get that slow? Resuming, how can i refresh pivot tables without getting the links, but with a VBA code? Thanks in advance, hope i wasnt very confusing

Best Regards, Hugo

Answer

in Excel 2007... right-click on the pivot table, and choose pivot table options. On the Data tab, there's a checkbox for "refresh data when opening the file." If you uncheck this box, then when you open the workbook, it won't update the pivot table link to file B, so it will open quicker.

Still in the PivotTableOptions popup, at the top, you'll see the PivotTable name. Once you know the pivot table name, then you can use vba to refresh a pivot table individually. To refresh PivotTable1 on Sheet1, use:

Sub refreshPivotTbl()

   Dim pvt As PivotTable
   Dim wks As Worksheet
   Set wks = ThisWorkbook.Sheets("Sheet1")
   Set pvt = wks.PivotTables("PivotTable1")
   pvt.RefreshTable

End Sub