Refreshing external data on individual sheets in a specific sequence.

[nayannilank]

Hi All,

I have a workbook consisting of 10 worksheets. First three worksheets contains Pivot Table, which gets the data from 4th, 5th and 6th worksheets. 4th, 5th and 6th worksheets gets the data (using a web query) from an external CSV file. I want to have a "Refresh Data" on the fist sheet, which is when clicked, refreshes the external data and the pivot tables in the following sequence :-
Refresh the data on 4th Sheet.
Refresh the data on 5th Sheet.
Refresh the data on 6th Sheet.
Refresh the Pivot Tables on 1st sheet.
Refresh the Pivot Tables on 2nd sheet.
Refresh the Pivot Tables on 3rd sheet.

I used "ThisWorkbook.RefreshAll", but it refreshes the data in one go, not in the required sequence. I also tried "Sheets(index).Calculate" for different sheets, but it does not refreshes the external data.

Any help in writing the macro/subroutine to achieve this functionality would be highly appreciated.

Thanks,
Nayan

[p45cal]

The following code may be more involved than necessary since it takes account of the plurals in your message:
"Refresh the Pivot Tables on 1st sheet.
Refresh the Pivot Tables on 2nd sheet.
Refresh the Pivot Tables on 3rd sheet."
It also assumes there may be more than one web/database query on each of the other sheets:[vba]Sub blah()
For Each qrytbl In Sheets("4thSheet").QueryTables
qrytbl.Refresh BackgroundQuery:=False
Next qrytbl
For Each qrytbl In Sheets("5thSheet").QueryTables
qrytbl.Refresh BackgroundQuery:=False
Next qrytbl
For Each qrytbl In Sheets("6thSheet").QueryTables
qrytbl.Refresh BackgroundQuery:=False
Next qrytbl
For Each pvtbl In Sheets("1st Sheet").PivotTables
pvtbl.PivotCache.Refresh
Next pvtbl
For Each pvtbl In Sheets("2nd Sheet").PivotTables
pvtbl.PivotCache.Refresh
Next pvtbl
For Each pvtbl In Sheets("3rd Sheet").PivotTables
pvtbl.PivotCache.Refresh
Next pvtbl
End Sub[/vba] Now just add a button to the sheet and assign this macro to it.

[nayannilank]

I also tried the same and it worked.

Earlier I was refreshing the Pivot Table not the Pivot Cache.

Thanks a lot for your help.