🧩 Excel automation examples

VBA Refresh PivotTables Examples

Refresh all PivotTables before sending or exporting a report. Use these examples as practical patterns, then adjust worksheet names, table names and ranges for your own workbook.

Updated 2026-06-125 practical examplesCopy-ready code

Syntax or pattern

This is the basic pattern for this type of automation. Copy the code, then replace workbook-specific names before running it.

Sub MacroName() ... End Sub

✍️

5 practical examples

1

Basic refresh pivottables macro

Use this pattern as a starting point for refresh all pivottables before sending or exporting a report.

Sub ExampleMacro()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Range("A1").Value = "Updated by macro"
End Sub

Adjust worksheet names, table names and ranges to match your own workbook before using this pattern.

2

Work with a named worksheet

Target a specific worksheet so the macro does not depend on the active sheet.

Sub UseNamedSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("A1").Value = "Ready"
End Sub

Adjust worksheet names, table names and ranges to match your own workbook before using this pattern.

3

Loop through rows

Apply the idea to multiple rows in a report or imported dataset.

Sub LoopRows()
    Dim ws As Worksheet, lastRow As Long, r As Long
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To lastRow
        If ws.Cells(r, "A").Value <> "" Then ws.Cells(r, "B").Value = "Checked"
    Next r
End Sub

Adjust worksheet names, table names and ranges to match your own workbook before using this pattern.

4

Add a confirmation message

Show a clear message after the task finishes.

Sub ConfirmDone()
    'Run your automation steps here
    MsgBox "The automation is complete.", vbInformation
End Sub

Adjust worksheet names, table names and ranges to match your own workbook before using this pattern.

5

Simple error handling

Use a safe error handler so users get a useful message if something fails.

Sub SafeMacro()
    On Error GoTo CleanFail
    ThisWorkbook.Worksheets("Data").Range("A1").Value = "Updated"
    Exit Sub
CleanFail:
    MsgBox "The macro could not finish: " & Err.Description, vbExclamation
End Sub

Adjust worksheet names, table names and ranges to match your own workbook before using this pattern.

Safety notes before you run automation

Test the code on a copy of the workbook first.
Save important files before running code that deletes, moves, formats or emails data.
Check worksheet names, table names and range references before copying the example.

Common mistakes to avoid

Running automation on the only copy of a workbook instead of a backup.
Hardcoding sheet names, table names, or ranges without checking that they exist.
Forgetting that macros and scripts can change many cells very quickly.
Using Select and Activate when direct worksheet/range references are safer.

FAQ

Is this VBA macro safe to copy?

Use the code as a learning pattern. Test it on a copy of your workbook before running it on important files.

Why does the code not work in my workbook?

Usually the sheet, table, range, or column name is different. Update names and test on a small sample first.

Should I use VBA or Office Scripts?

Use VBA for desktop Excel macros and legacy workbooks. Use Office Scripts for Excel on the web and cloud automation workflows.

💡 Useful resources

Here are some ideas for you

Optional resources that may help if you are learning Excel automation, building reports, or automating spreadsheets often.

📘
Excel VBA books
Learn macro patterns, objects, ranges and real automation examples.
See ideas
🧩
Office Scripts resources
Practice automating Excel on the web with repeatable script patterns.
See ideas
⌨️
Productivity keyboards
Helpful for writing code, formulas and documentation for long sessions.
See ideas
🖥️
External monitors
View the workbook, VBA editor, script editor and notes side by side.
See ideas
💾
External backup drives
Useful for keeping backup copies of automation workbooks and reports.
See ideas
📒
Workflow notebooks
Sketch macro steps, report logic and safety checks before coding.
See ideas

Some links in this section may be affiliate links. Choose only what is useful for your own work.