Refresh Data and Pivot Tables in Excel Using VBA

The following Excel 2003 VBA macro can be used to refresh external data lists and the associated pivot tables within a workbook programmatically. This procedure is useful to provide users with an ability to generate Excel reports as needed with the latest data. Later versions of Excel can simply use ThisWorkbook.RefreshAll.

Sub refresh_data()
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
Dim lo As ListObject

'Change setting in each Pivot Table to remove stale or missing items
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'Refresh each Pivot Cache to clear stale entries
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

'Refresh each List Object to retrieve latest external data
For Each ws In ActiveWorkbook.Worksheets
For Each lo In ws.ListObjects
lo.Refresh
Next
Next

'Refresh all Pivot Tables
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

Calculate Quarter Number In Excel

The following formula may be used to calculate the quarter number, e.g., 1, 2, 3, or 4, for a given date in a regular calendar year. This formula uses a combination of the ROUNDUP and MONTH functions. The expected results of the formula are January through March are Q1, April through June are Q2, July through September are Q3, and October through December are Q4.

In the results, Column B titled Quarter (#) is produced using the following formula.

=ROUNDUP(MONTH(\$A2)/3,0)

This formula uses the MONTH function to obtain the month number (1 through 12) for the given date and divides it by 3. As an example, March (month 3) divided by 3 produces the result 1 which is the correct quarter number. January (month 1) divided by 3 produces the result 0.33 which is not the correct quarter number. The ROUNDUP function rounds fractional numbers to the next highest whole number. Using January again, the 0.33 is rounded up to 1 to produce the correct result. The formula divides by 3 because there are 3 months in every quarter.