How do I skip a specific tab, like a summary sheet, while looping?

Add an If ws.Name <> "Summary" Then check inside the loop before the consolidation logic runs, so the summary tab itself is never re-read as a data source.

Does For Each ws In Worksheets include chart sheets?

No. Worksheets only returns worksheet objects. Use Sheets instead if the workbook also contains chart sheets you need to iterate over.

What is the fastest way to find each sheet's last used row inside the loop?

Use ws.Cells(ws.Rows.Count, 1).End(xlUp).Row on the column that is always populated. It is the most reliable single-column method for finding the last row per tab.

VBA Loop Through Worksheets: Build a Consolidated P&L

Q: How do I loop through only a specific range of monthly tabs?

Reference the sheets by index range, such as For i = 2 To 13, or build an array of the exact tab names and loop through the array instead of the full Worksheets collection.

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The For Each Worksheet Loop

Run this from any standard module:

Option Explicit

Sub ConsolidateMonthlyPandL()
    Dim ws As Worksheet
    Dim wsSummary As Worksheet
    Dim nextRow As Long

    Set wsSummary = ThisWorkbook.Sheets("Summary")
    nextRow = 2

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            wsSummary.Cells(nextRow, 1).Value = ws.Name
            wsSummary.Cells(nextRow, 2).Value = ws.Range("B20").Value ' Net Profit cell
            nextRow = nextRow + 1
        End If
    Next ws
End Sub

Every tab except the summary contributes one row — no manual navigation, no risk of skipping a month.

Download the Example Workbook — Free

The working .xlsm file with 12 monthly tabs, a Summary tab, and this consolidation macro already wired up — open it, inspect the code, and adapt it to your own tab names.

Where the Code Goes

This lives in a standard module, run on demand once all twelve monthly tabs are populated. It pairs well with Workbooks.Add if each month starts life as a fresh working file before being copied into this consolidation workbook.

Finance Use Case: Twelve Tabs, One P&L

A rolling twelve-month workbook with one tab per month is a common shared-drive structure, and manually re-typing each month's net profit into a summary sheet invites transposition errors. The loop reads directly from a known cell reference on every tab, so the summary is always a live reflection of what is actually on each sheet, not what someone remembered to copy across.

This matters most at exactly the moment finance teams are least equipped to catch a mistake: the final days before a board pack or lender covenant test is due, when every monthly tab has already been touched by two or three different preparers. A single mistyped cell reference during manual consolidation can shift a full month's net profit into the wrong row without anyone noticing until the totals stop reconciling. Rebuilding the summary with a loop instead of copy-paste removes that entire failure mode — the macro reads the same cell on every tab, every time, with no room for a fat-fingered row.

Pulling More Than One Cell Per Sheet

For a full row of line items rather than a single figure, copy a defined range from each tab instead of a single cell:

ws.Range("B5:B15").Copy
wsSummary.Cells(nextRow, 2).PasteSpecial xlPasteValues

Copy Paste Values Only covers why xlPasteValues matters here — pasting formulas across sheets breaks the moment a source tab is renamed or reordered.

Common Mistakes

Forgetting to exclude the summary tab itself, which then reads its own blank output back into the loop.
Hardcoding a cell reference like B20 that shifts if a row is inserted on any individual monthly tab.
Using Worksheets when the workbook also contains chart sheets that need to be included — use Sheets instead in that case.
Not resetting nextRow before re-running the macro, which appends duplicate rows instead of overwriting the summary.

Skip the manual amortisation rebuild every month

FinDataPro Prepaid Amortisation Engine generates each month's schedule automatically, so there is one less tab you need this loop to pull from by hand.

See the Prepaid Amortisation Engine

Once the summary is built, see Loop Through Range of Cells for flagging outliers in it, or Close Workbook Without Saving for batch-closing the monthly source files once consolidated.

Full reference: Microsoft Learn Worksheets collection reference.

Frequently Asked Questions

How do I skip a specific tab while looping?

Add an If ws.Name <> "Summary" Then check before the consolidation logic runs.

Does this loop include chart sheets?

No. Worksheets only returns worksheet objects — use Sheets if chart sheets need to be included too.

What is the fastest way to find each sheet's last row?

ws.Cells(ws.Rows.Count, 1).End(xlUp).Row on a column that is always populated.

Can I loop through only some of the tabs?

Reference sheets by index range, or build an array of the exact tab names and loop through that instead.

Conclusion

A single For Each loop replaces a repetitive, error-prone copy-paste routine with one consistent pass across every monthly tab. Exclude the summary sheet, reference cells by name where possible, and pair it with Loop Through Range of Cells to flag anything unusual once consolidated. If the monthly schedules themselves are the bottleneck, see the Prepaid Amortisation Engine.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.

Discussion

Comments are moderated and appear once approved.