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.

Calculate Quarter Number in Excel (Results)
Calculate Quarter Number in Excel (Results)

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.

Calculate Quarter Number in Excel (Intermediate Formula Results)
Calculate Quarter Number in Excel (Intermediate Formula Results)

Also in the results, Column C titled Quarter (Q#), is produced using the following formula which adds a “Q” prefix to the quarter number.

=CONCATENATE("Q",ROUNDUP(MONTH($A2)/3,0))

The next image provides the formula view for this entire example.

Calculate Quarter Number in Excel (Formula)
Calculate Quarter Number in Excel (Formula)