Loop Through Worksheets, Sheets and Chart Sheets in Excel VBA

To loop through worksheets in VBA you first need to know which kind of sheet you are looping over, because Excel has two: worksheets with cells, and chart sheets that hold a single chart. Pick the wrong collection and your macro either skips sheets or crashes. In this tutorial you will see the difference, create a chart sheet two ways, and write three short macros that loop through the Worksheets, Sheets and Charts collections, with the exact results each one returns on the practice file.

⬇️  Download the Practice File
ZIP with one macro-enabled workbook · .xlsm · enable macros to run the code

Watch the Video Tutorial

In the video PK explains chart sheets versus worksheets, creates and resizes a chart sheet, checks TypeName and the sheet counts, then writes and runs all three loops.

What You Will Learn

• The difference between a worksheet, a chart sheet and the Sheets collection.
• Two ways to create a chart sheet: the F11 shortcut and Move Chart.
• How to check a sheet’s type with TypeName and count each collection.
• Three ready-to-use loops for worksheets only, all sheets and chart sheets only.
• The common mistakes that make sheet loops fail, and how to avoid them.

Worksheets vs Chart Sheets: The Difference

Every worksheet is a sheet, but not every sheet is a worksheet. Excel has two types of sheets:

• Worksheet: the normal sheet with rows, columns, gridlines and cells. You type data and formulas here and create charts on it.
• Chart sheet: a sheet that holds a single chart and nothing else. It is one printable page. You can change the chart design, change the chart type or switch rows and columns, but you cannot enter data in it.

VBA mirrors this with three collections:

• Worksheets – worksheets only.
• Charts – chart sheets only.
• Sheets – everything, worksheets and chart sheets together.

Part 1 – Create and Resize a Chart Sheet

The practice file already has three chart sheets, but here is how they were made.

Step 1: Create a chart sheet with F11

Select your data (in the practice file, the Day and Sales table in A1:B8 on Sheet1) and press F11. Excel creates a column chart of the selection on a brand-new chart sheet. In the video this is how Chart3 appears.

Step 2: Move an existing chart to its own sheet

If the chart already sits on a worksheet, select it and right-click Move Chart, or use Chart Design > Move Chart (Design tab in older versions). In the Move Chart window choose New sheet, type a name such as My New Chart Sheet and click OK. The chart moves to the new chart sheet and disappears from the worksheet.

Step 3: Resize the chart sheet

A chart sheet is sized as a page. Go to Page Layout > Size and pick a paper size such as Legal or A3, and use Orientation to switch between Landscape and Portrait. The chart grows or shrinks with the page.

Part 2 – Check the Sheet Type and Count in VBA

Step 4: Open the VBA editor and insert a module

Go to Developer > Visual Basic (or press Alt+F11), then Insert > Module. In the Project Explorer you can already see a difference: chart sheets have a different icon from worksheets.

Step 5: Ask VBA what type the active sheet is

Run this test macro on different tabs:

Sub Test()
MsgBox TypeName(ActiveSheet)
End Sub

On Sheet2 it shows Worksheet. Activate Chart3 and run it again and it shows Chart. That is the object type VBA uses for a chart sheet.

Step 6: Compare the counts

MsgBox ThisWorkbook.Worksheets.Count   'returns 2
MsgBox ThisWorkbook.Sheets.Count       'returns 5

The workbook has five tabs, but Worksheets.Count returns 2 because it ignores the three chart sheets. Sheets.Count returns 5 because it includes both kinds. Keep these numbers in mind; the loops below return exactly these sheets.

Part 3 – Loop Through Worksheets in VBA

Step 7: Loop through worksheets only

Use a For Each loop over the Worksheets collection, with a variable declared as Worksheet:

Sub LoopthroughinWorksheets()

Dim i As Integer
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
    MsgBox wsh.Name
Next

End Sub

Result on the practice file: Sheet2, then Sheet1. Chart3, My New Chart Sheet and Chart1 are skipped.

How the code works

• Dim wsh As Worksheet creates a variable that can hold one worksheet at a time. (The Dim i As Integer line is not used in this macro, so you can delete it.)
• For Each wsh In ThisWorkbook.Worksheets hands the loop each worksheet in tab order. ThisWorkbook means the workbook that contains the code, not whichever workbook happens to be active.
• MsgBox wsh.Name shows the sheet’s tab name. Replace this line with any action you need, such as formatting, protecting or clearing each sheet.
• Next moves to the next worksheet until there are none left.

Part 4 – Loop Through All Sheets (Including Chart Sheets)

Step 8: Loop by index with Sheets.Count

To visit every tab, loop with a counter from 1 to the number of sheets:

Sub LoopthroughinSheets()

Dim i As Integer

For i = 1 To ThisWorkbook.Sheets.Count
    MsgBox ThisWorkbook.Sheets(i).Name

Next i

End Sub

Result: Sheet2, Chart3, My New Chart Sheet, Sheet1, Chart1 – all five tabs, in the order they appear in the tab bar.

How the code works

• ThisWorkbook.Sheets.Count returns 5, so the loop runs five times.
• Sheets(i) picks a sheet by its position: Sheets(1) is the first tab, Sheets(2) the second, and so on.
• Because the Sheets collection holds both types, the loop includes chart sheets as well as worksheets.

Looping by index is also useful when you need the position, for example to skip the first sheet with For i = 2 To ....

Part 5 – Loop Through Chart Sheets Only

Step 9: Use the Charts collection

Sub LoopThroughinChartsheets()

Dim cht As Chart

For Each cht In ThisWorkbook.Charts

    MsgBox cht.Name
Next

End Sub

Result: Chart3, My New Chart Sheet, Chart1. Only the chart sheets are returned.

How the code works

• Dim cht As Chart matches what TypeName told you in Step 5: a chart sheet is a Chart object.
• ThisWorkbook.Charts is the collection of chart sheets, so worksheets are ignored automatically.

Charts on a Worksheet Are Different (Addition)

This section is an addition to the video, based on standard Excel VBA behaviour. The Charts collection only holds chart sheets. A chart that sits on a worksheet (an embedded chart) lives in that worksheet’s ChartObjects collection instead, so the loop in Step 9 will not find it. To list embedded charts, loop through the worksheets and then through each sheet’s chart objects:

Sub LoopThroughEmbeddedCharts()
Dim wsh As Worksheet
Dim co As ChartObject
For Each wsh In ThisWorkbook.Worksheets
    For Each co In wsh.ChartObjects
        Debug.Print wsh.Name & " - " & co.Name
    Next co
Next wsh
End Sub

Debug.Print writes to the Immediate window (Ctrl+G), which is easier than clicking OK on a message box for every item.

Common Errors and Fixes

Run-time error 13: Type mismatch

You wrote For Each wsh In ThisWorkbook.Sheets with wsh declared as Worksheet. As soon as the loop reaches a chart sheet, VBA cannot put a Chart into a Worksheet variable. Loop through Worksheets instead, or declare the variable as Object when you really need every sheet. (Addition, not shown in the video.)

The macro misses some sheets

You used Worksheets but the workbook has chart sheets, or you used Charts and expected worksheets. Check the counts from Step 6 to see which collection you need.

The loop runs on the wrong workbook

ThisWorkbook is always the file that holds the code. If you keep the macro in your Personal Macro Workbook and want it to work on the file you are looking at, use ActiveWorkbook instead.

Tips

• Put Option Explicit at the top of each module, as the practice file does, so VBA flags any undeclared or misspelled variable.
• Use Long instead of Integer for counters in new code. An Integer stops at 32,767, while a Long handles far larger counts. It is also the native 32-bit type. (Addition.)
• Use TypeName inside a Sheets loop when you need to treat worksheets and chart sheets differently.
• Swap the MsgBox for real work: write each name to a list, export each sheet to PDF or protect every sheet.
• Microsoft’s reference for the For Each…Next statement covers the loop syntax in detail.

Want It Ready-Made?

If you would rather use finished VBA tools than write every macro yourself, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles eight ready-made Excel VBA templates, and you can browse all Excel VBA tools there.

Related Tutorials

• Export All Excel Worksheets in Separate PDF Files
• Delete All Blank Worksheets Using a Personal Macro
• Macro to Create Multiple Worksheets from a Selection
• Protect an Excel Worksheet as UserInterfaceOnly
• 33 Useful VBA Shortcut Keys

Frequently Asked Questions

What is the difference between Sheets and Worksheets in VBA?

Worksheets contains only normal worksheets with cells. Sheets contains every sheet in the workbook, including chart sheets. In the practice file Worksheets.Count returns 2 and Sheets.Count returns 5.

How do I loop through all worksheets in a workbook?

Declare a Worksheet variable and use For Each with ThisWorkbook.Worksheets, then put your action inside the loop. Step 7 shows the full macro.

Why does my sheet loop give a Type mismatch error?

You are looping through Sheets with a variable declared as Worksheet, and the workbook contains a chart sheet. Loop through Worksheets instead, or declare the variable as Object.

How do I loop through only the chart sheets?

Declare a variable As Chart and use For Each with ThisWorkbook.Charts. It returns chart sheets only and skips every worksheet.

Does the Charts collection include charts placed on a worksheet?

No. Charts holds chart sheets only. Embedded charts belong to the ChartObjects collection of the worksheet they sit on, so loop through each worksheet and then its ChartObjects.

How do I create a chart sheet quickly?

Select your data and press F11. Excel creates a chart of the selection on a new chart sheet. To move an existing chart, use Move Chart and choose New sheet.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To loop through worksheets in VBA, choose the collection that matches the sheets you want: Worksheets for sheets with cells, Charts for chart sheets and Sheets for everything. The practice file shows the difference clearly, returning 2, 3 and 5 sheets for the three loops. Download it, run each macro and then replace the MsgBox with the task you want to automate.