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
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:
Dim pvt As PivotTable Dim wks As Worksheet Set wks = ThisWorkbook.Sheets("Sheet1") Set pvt = wks.PivotTables("PivotTable1") pvt.RefreshTable