Refreshing external data on individual sheets in a specific sequen

[Nayan]

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

 

[They call me Trinity]

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

Hi Nayan

I wrote a couple of subs a few years ago when i was experiencing a similiar
problem.

If you call UpdateQueryTablesInWorkBook (to update your web queries) and
then UpdatePivotTablesInWorkBook (to update your pivottables) it ought to
solve your problem.

'Updates all queries in the Workbook
Public Sub UpdateQueryTablesInWorkBook()
Dim s As Worksheet
Dim q As QueryTable

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh
Next q
Next s
End Sub

'Updates all Pivottables in the workbook
Public Sub UpdatePivotTablesInWorkBook()
Dim s As Worksheet
Dim p As PivotTable

For Each s In ActiveWorkbook.Worksheets
For Each p In s.PivotTables
With p.PivotCache
.Refresh
End With
Next p
Next s
End Sub

Regards
Marinus Calitz