In this VBA Tutorial, you learn how to refer to, and work with, sheets and worksheets in macros. This includes:
- How to refer to all sheets in a workbook.
- How to refer to all worksheets in a workbook.
- How to refer to the active sheet.
- How to refer to a sheet by its index number.
- How to refer to a worksheet by its index number.
- How to refer to a sheet by its name.
- How to refer to a worksheet by its name.
- How to refer to a sheet by its code name.
- How to refer to several sheets.
- How to refer to several worksheets.
- How to loop through all sheets in a workbook with the For Each… Next loop.
- How to loop through all worksheets in a workbook with the For Each… Next loop.
- How to loop through all sheets in a workbook with the For… Next loop.
- How to loop through all worksheets in a workbook with the For… Next loop.
- How to loop through all sheets in a workbook in reverse order.
- How to loop through all worksheets in a workbook in reverse order.
This VBA Tutorial is accompanied by an Excel workbook containing the macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Alternatively, you can access all the files that accompany my Tutorials here.
Table of Contents
Related Excel VBA and Macro Tutorials
The following VBA and Macro Tutorials may help you better understand and implement the contents below:
- General VBA constructs and structures:
- Learn the basics of working with macros here.
- Learn about basic VBA terms and constructs here.
- Learn how to enable or disable macros here.
- Learn how to work with the Visual Basic Editor here.
- Learn how to create Sub procedures here.
- Learn how to create object references here.
- Learn how to work with object properties here.
- Learn how to work with object methods here.
- Learn how to declare and work with variables here.
- Learn about VBA data types here.
- Learn how to work with arrays here.
- Learn how to work with loops here.
- Practical VBA applications and macro examples:
- Learn how to delete sheets and worksheets here.
You can find additional VBA and Macro Tutorials in the Archives.
#1: Refer to all sheets in workbook
VBA code to refer to all sheets in workbook
To refer to all sheets in a workbook with VBA, use an object reference with the following structure:
Workbook.Sheets
Process to refer to all sheets in workbook
To refer to all sheets in a workbook with VBA, follow these steps:
- Identify the workbook containing the sheets (Workbook).
- Refer to the Sheets collection representing all sheets in Workbook (Sheets).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Sheets
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
Macro example to refer to all sheets in workbook
The following macro example displays a message box (MsgBox) with the number of sheets (Sheets.Count) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetsCollection() 'source: https://powerspreadsheets.com/ 'displays a message box with the number of sheets in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'display message box with number of sheets in this workbook MsgBox ThisWorkbook.Sheets.Count End Sub
Effects of executing macro example to refer to all sheets in workbook
The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5) and 5 chart sheets (Chart1 through Chart5). Therefore, Excel displays a message box with the number 10.
#2: Refer to all worksheets in workbook
VBA code to refer to all worksheets in workbook
To refer to all worksheets in a workbook with VBA, use an object reference with the following structure:
Workbook.Worksheets
Process to refer to all worksheets in workbook
To refer to all worksheets in a workbook with VBA, follow these steps:
- Identify the workbook containing the worksheets (Workbook).
- Refer to the Sheets collection representing all worksheets in Workbook (Worksheets).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Worksheets
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
Macro example to refer to all worksheets in workbook
The following macro example displays a message box (MsgBox) with the number of worksheets (Worksheets.Count) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetsCollection() 'source: https://powerspreadsheets.com/ 'displays a message box with the number of worksheets in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'display message box with number of worksheets in this workbook MsgBox ThisWorkbook.Worksheets.Count End Sub
Effects of executing macro example to refer to all worksheets in workbook
The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5). Therefore, Excel displays a message box with the number 5.
#3: Refer to active sheet
VBA code to refer to active sheet
To refer to the active sheet with VBA, use an object reference with the following structure:
Workbook.ActiveSheet
Process to refer to active sheet
To refer to the active sheet with VBA, follow these steps:
- Identify the workbook containing the sheet (Workbook). If you don't identify Workbook, VBA works with the active workbook.
- Refer to the active sheet in Workbook (ActiveSheet).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the active sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
If you don't specify Workbook when referring to the active sheet with ActiveSheet, VBA works with the active workbook (the workbook on top).
Item: ActiveSheet
The ActiveSheet returns an object representing the active sheet (the sheet on top) in Workbook, as follows:
- If you specify Workbook, ActiveSheet returns an object representing the active sheet in Workbook.
- If you don't specify Workbook, ActiveSheet returns an object representing the active sheet in the active workbook (the workbook on top).
Macro example to refer to active sheet
The following macro example displays a message box (MsgBox) with the name (Name) of the active sheet in the active workbook (ActiveSheet).
Sub referToActiveSheet() 'source: https://powerspreadsheets.com/ 'displays a message box with the name of the active sheet 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'display message box with name of active sheet MsgBox ActiveSheet.Name End Sub
Effects of executing macro example to refer to active sheet
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of the active sheet (Sheet1).
#4: Refer to sheet by index number
VBA code to refer to sheet by index number
To refer to a sheet by its index number with VBA, use an object reference with the following structure:
Workbook.Sheets(SheetIndexNumber)
Process to refer to sheet by index number
To refer to a sheet by its index number with VBA, follow these steps:
- Identify the workbook containing the sheet (Workbook).
- Identify the sheet by its index number (Sheets(SheetIndexNumber)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Sheets(SheetIndexNumber)
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
SheetIndexNumber is the index number of the sheet you refer to. This index number represents the position of the sheet in the tab bar of Workbook, from left to right. For these purposes, the count usually includes:
- Hidden sheets; and
- Both chart sheets and worksheets.
Therefore, Sheets(SheetIndexNumber) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose index number is SheetIndexNumber.
Macro example to refer to sheet by index number
The following macro example activates (Activate) the fifth sheet (Sheets(5)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetIndex() 'source: https://powerspreadsheets.com/ 'activates the fifth sheet in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'activate fifth sheet in this workbook ThisWorkbook.Sheets(5).Activate End Sub
Effects of executing macro example to refer to sheet by index number
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet1. As expected, Excel activates the fifth sheet (Chart1).
#5: Refer to worksheet by index number
VBA code to refer to worksheet by index number
To refer to a worksheet by its index number with VBA, use an object reference with the following structure:
Workbook.Worksheets(WorksheetIndexNumber)
Process to refer to worksheet by index number
To refer to a worksheet by its index number with VBA, follow these steps:
- Identify the workbook containing the worksheet (Workbook).
- Identify the worksheet by its index number (Worksheets(WorksheetIndexNumber)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Worksheets(WorksheetIndexNumber)
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
WorksheetIndexNumber is the index number of the worksheet you refer to. This index number represents the position of the worksheet in the tab bar of Workbook, from left to right. For these purposes, the count usually:
- Includes hidden worksheets; but
- Doesn't include chart sheets.
Therefore, Worksheets(WorksheetIndexNumber) returns an individual Worksheet object representing the worksheet whose index number is WorksheetIndexNumber.
Macro example to refer to worksheet by index number
The following macro example activates (Activate) the first worksheet (Worksheets(1)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetIndex() 'source: https://powerspreadsheets.com/ 'activates the first worksheet in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'activate first worksheet in this workbook ThisWorkbook.Worksheets(1).Activate End Sub
Effects of executing macro example to refer to worksheet by index number
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet5. As expected, Excel activates the first worksheet (Sheet1).
#6: Refer to sheet by name
VBA code to refer to sheet by name
To refer to a sheet by its name with VBA, use an object reference with the following structure:
Workbook.Sheets("SheetName")
Process to refer to sheet by name
To refer to a sheet by its name with VBA, follow these steps:
- Identify the workbook containing the sheet (Workbook).
- Identify the sheet by its name (Sheets(“SheetName”)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Sheets(“SheetName”)
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
“SheetName” is a string representing the name of the sheet you refer to, as displayed in the sheet's tab. If you explicitly declare a variable to represent “SheetName”, you can usually declare it as of the String data type.
Therefore, Sheets(“SheetName”) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose name is SheetName.
Macro example to refer to sheet by name
The following macro example activates (Activate) the sheet named “Chart1” (Sheets(“Chart1”)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetName() 'source: https://powerspreadsheets.com/ 'activates the sheet named "Chart1" in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'activate Chart1 in this workbook ThisWorkbook.Sheets("Chart1").Activate End Sub
Effects of executing macro example to refer to sheet by name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet1. As expected, Excel activates Chart1.
#7: Refer to worksheet by name
VBA code to refer to worksheet by name
To refer to a worksheet by its name with VBA, use an object reference with the following structure:
Workbook.Worksheets("WorksheetName")
Process to refer to worksheet by name
To refer to a worksheet by its name with VBA, follow these steps:
- Identify the workbook containing the worksheet (Workbook).
- Identify the worksheet by its name (Worksheets(“WorksheetName”)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Worksheets(“WorksheetName”)
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
“WorksheetName” is a string representing the name of the worksheet you refer to, as displayed in the worksheet's tab. If you explicitly declare a variable to represent “WorksheetName”, you can usually declare it as of the String data type.
Therefore, Worksheets(“WorksheetName”) returns an individual Worksheet object representing the worksheet whose name is WorksheetName.
Macro example to refer to worksheet by name
The following macro example activates (Activate) the worksheet named “Sheet1” (Worksheets(“Sheet1”)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetName() 'source: https://powerspreadsheets.com/ 'activates the worksheet named "Sheet1" in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'activate Sheet1 in this workbook ThisWorkbook.Worksheets("Sheet1").Activate End Sub
Effects of executing macro example to refer to worksheet by name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Chart1. As expected, Excel activates Sheet1.
#8: Refer to sheet by code name
VBA code to refer to sheet by code name
To refer to a sheet by its code name with VBA, use the sheet's code name:
SheetCodeName
Process to refer to sheet by code name
To refer to a sheet by its code name with VBA, use the sheet's code name.
VBA statement explanation
Item: SheetCodeName
SheetCodeName is the code name of the sheet you refer to.
You can use a sheet's code name instead of an object reference (such as the ones I explain in other sections of this VBA Tutorial) returning the Chart or Sheet object you refer to.
Macro example to refer to sheet by code name
The following macro example activates (Activate) the worksheet whose code name is Sheet1 (Sheet1).
Sub referToSheetCodeName() 'source: https://powerspreadsheets.com/ 'activates Sheet1 in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'activate Sheet1 in this workbook Sheet1.Activate End Sub
Effects of executing macro example to refer to sheet by code name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet5. As expected, Excel activates Sheet1 (both the name and code name are Sheet1).
#9: Refer to several sheets
VBA code to refer to several sheets
To refer to several sheets with VBA, use an object reference with the following structure:
Workbook.Sheets(Array(SheetList))
Process to refer to several sheets
To refer to several sheets with VBA, follow these steps:
- Identify the workbook containing the sheets (Workbook).
- Obtain an array with the index numbers or names of the sheets you refer to (Array(SheetList)).
- Identify the sheets (Sheets(Array(SheetList))).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Sheets(Array(SheetList))
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
The Array function (Array(SheetList)) returns a Variant containing an array with the index numbers or names of the sheets you refer to.
SheetList is the argument list of the Array function, which contains a comma-delimited list of the values you assign to each of the elements in the array returned by Array. When referring to several sheets, you can usually identify the specific objects in the Sheets collection you work with using the appropriate index number or sheet name, as follows:
- The index number represents the position of a sheet in the tab bar of Workbook, from left to right. For these purposes, the count usually includes:
- Hidden sheets; and
- Both chart sheets and worksheets.
- The sheet name is that displayed in the sheet's tab.
Therefore, Sheets(Array(SheetList)) represents the chart sheets or worksheets you specify in SheetList.
Macro example to refer to several sheets
The following macro example moves (Move) the first sheet, the sheet named “Sheet3” and the sheet named “Chart1” (Sheets(Array(1, “Sheet3”, “Chart1”))) in the workbook where the macro is stored (ThisWorkbook) to the end of the workbook (After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).
Sub referToSeveralSheets() 'source: https://powerspreadsheets.com/ 'moves several sheets to end of this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'move the first sheet, "Sheet3" and "Chart1" to end of this workbook ThisWorkbook.Sheets(Array(1, "Sheet3", "Chart1")).Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) End Sub
Effects of executing macro example to refer to several sheets
The following GIF illustrates the results of executing the macro example. As expected, Sheet1 (the first sheet), Sheet3 and Chart1 are moved to the end of the workbook.
#10: Refer to several worksheets
VBA code to refer to several worksheets
To refer to several worksheets with VBA, use an object reference with the following structure:
Workbook.Worksheets(Array(WorksheetList))
Process to refer to several worksheets
To refer to several worksheets with VBA, follow these steps:
- Identify the workbook containing the worksheets (Workbook).
- Obtain an array with the index numbers or names of the worksheets you refer to (Array(WorksheetList)).
- Identify the worksheets (Sheets(Array(WorksheetList))).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
Item: Worksheets(Array(WorksheetList))
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
The Array function (Array(WorksheetList)) returns a Variant containing an array with the index numbers or names of the worksheets you refer to.
WorksheetList is the argument list of the Array function, which contains a comma-delimited list of the values you assign to each of the elements in the array returned by Array. When referring to several worksheets, you can usually identify the specific objects in the Worksheets collection you work with using the appropriate index number or sheet name, as follows:
- The index number represents the position of a worksheet in the tab bar of Workbook, from left to right. For these purposes, the count usually:
- Includes hidden sheets; but
- Doesn't include chart sheets.
- The worksheet name is that displayed in the worksheet's tab.
Therefore, Sheets(Array(WorksheetList)) represents the chart sheets or worksheets you specify in WorksheetList.
Macro example to refer to several worksheets
The following macro example moves (Move) the worksheets named “Sheet1”, “Sheet2” and “Sheet3” (Worksheets(Array(“Sheet1”, “Sheet2”, “Sheet3”))) in the workbook where the macro is stored (ThisWorkbook) after the last worksheets in the workbook (After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).
Sub referToSeveralWorksheets() 'source: https://powerspreadsheets.com/ 'moves several worksheets after the last worksheet in this workbook 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'move "Sheet1", "Sheet2" and "Sheet3" after the last worksheet in this workbook ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) End Sub
Effects of executing macro example to refer to several worksheets
The following GIF illustrates the results of executing the macro example. As expected, Sheet1, Sheet2 and Sheet3 are moved after the last worksheet in the workbook (Sheet5).
#11: Loop through all sheets in workbook with For Each… Next
VBA code to loop through all sheets in workbook with For Each… Next
To loop through all sheets in a workbook with a For Each… Next VBA loop, use a macro with the following statement structure:
For Each Sheet In Workbook.Sheets Statements Next Sheet
Process to loop through all sheets in workbook with For Each… Next
To loop through all sheets in a workbook with a For Each… Next VBA loop, follow these steps:
- Identify the workbook containing the sheets (Workbook).
- Identify the Sheets collection representing all sheets in Workbook (Sheets).
- Use an object variable to iterate through the Sheets in Workbook (Sheet).
- Execute a set of Statements for each Sheet in Workbook.
VBA statement explanation
Lines #1 and #3: For Each Sheet In Workbook.Sheets | Next Sheet
Item: For Each … In … | Next …
The For Each… Next statement repeats the Statements for each Sheet in Workbook.Sheets.
Item: Sheet
Object variable used to iterate through the Sheets in Workbook.
If you explicitly declare an object variable to represent Sheet, you can usually declare it as of the Variant or Object data type.
Item: Workbook.Sheets
Sheets collection through which the For Each… Next statement loops through.
Workbook is a Workbook object representing the Excel workbook containing the sheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
Therefore, For Each… Next loops through all sheets in Workbook.
Line #2: Statements
Statements that are executed for each Sheet in Workbook.Sheets.
Macro example to loop through all sheets in workbook with For Each… Next
The following macro example:
- Loops through each sheet in the workbook where the macro is stored (For Each iSheet In ThisWorkbook.Sheets | Next iSheet).
- Displays a message box (MsgBox) with the name (Name) of the current sheet (iSheet).
Sub loopThroughAllSheetsForEachNext() 'source: https://powerspreadsheets.com/ 'loops through all sheets in this workbook, and displays a message box with each sheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to iterate through all sheets Dim iSheet As Object 'loop through all sheets in this workbook For Each iSheet In ThisWorkbook.Sheets 'display message box with name of current sheet MsgBox iSheet.Name Next iSheet End Sub
Effects of executing macro example to loop through all sheets in workbook with For Each… Next
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each sheet (both worksheets and chart sheets) in the workbook.
#12: Loop through all worksheets in workbook with For Each… Next
VBA code to loop through all worksheets in workbook with For Each… Next
To loop through all worksheets in a workbook with a For Each… Next VBA loop, use a macro with the following statement structure:
For Each Worksheet In Workbook.Worksheets Statements Next Worksheet
Process to loop through all worksheets in workbook with For Each… Next
To loop through all worksheets in a workbook with a For Each… Next VBA loop, follow these steps:
- Identify the workbook containing the worksheets (Workbook).
- Identify the Sheets collection representing all worksheets in Workbook (Worksheets).
- Use an object variable to iterate through the worksheets in Workbook (Worksheet).
- Execute a set of Statements for each worksheet in Workbook.
VBA statement explanation
Lines #1 and #3: For Each Worksheet In Workbook.Worksheets | Next Worksheet
Item: For Each … In … | Next …
The For Each… Next statement repeats the Statements for each Worksheet in Workbook.Worksheets.
Item: Worksheet
Object variable used to iterate through the worksheets in Workbook.
If you explicitly declare an object variable to represent Worksheet, you can usually declare it as of the Worksheet object data type.
Item: Workbook.Worksheets
Sheets collection through which the For Each… Next statement loops through.
Workbook is a Workbook object representing the Excel workbook containing the worksheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
Therefore, For Each… Next loops through all worksheets in Workbook.
Line #2: Statements
Statements that are executed for each worksheet in Workbook.
Macro example to loop through all worksheets in workbook with For Each… Next
The following macro example:
- Loops through each worksheet in the workbook where the macro is stored (For Each iWorksheet In ThisWorkbook.Worksheets | Next iWorksheet).
- Displays a message box (MsgBox) with the name (Name) of the current sheet (iSheet).
Sub loopThroughAllWorksheetsForEachNext() 'source: https://powerspreadsheets.com/ 'loops through all worksheets in this workbook, and displays a message box with each worksheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to iterate through all worksheets Dim iWorksheet As Worksheet 'loop through all worksheets in this workbook For Each iWorksheet In ThisWorkbook.Worksheets 'display message box with name of current worksheet MsgBox iWorksheet.Name Next iWorksheet End Sub
Effects of executing macro example to loop through all worksheets in workbook with For Each… Next
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each worksheet in the workbook.
#13: Loop through all sheets in workbook with For… Next
VBA code to loop through all sheets in workbook with For… Next
To loop through all sheets in a workbook with a For… Next VBA loop, use a macro with the following statement structure:
For Counter = 1 To Workbook.Sheets.Count Statements Next Counter
Process to loop through all sheets in workbook with For… Next
To loop through all sheets in a workbook with a For… Next VBA loop, follow these steps:
- Identify the workbook containing the sheets (Workbook).
- Identify the Sheets collection representing all sheets in Workbook (Sheets).
- Count the number of sheets in the Sheets collection (Count).
- Execute a set of Statements a number of times equal to the number of Sheets in Workbook (For Counter = 1 To Workbook.Sheets.Count).
VBA statement explanation
Lines #1 and #3: For Counter = 1 To Workbook.Sheets.Count | Next Counter
Item: For … To … | Next …
The For… Next statement repeats the statements a number of times equal to the number of Sheets in Workbook.
Item: Counter
Numeric variable used as loop counter. If you explicitly declare Counter, you can usually declare it as of the Long data type.
Item: = 1
Counter's initial value.
Item: Workbook.Sheets.Count
Counter's end value, which is equal to the number of Sheets in Workbook.
Workbook is a Workbook object representing the Excel workbook containing the sheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
The Sheets.Count property returns the number of objects in the Sheets collection.
Therefore:
- Workbook.Sheets.Count returns the number of Sheets in Workbook; and
- For… Next loops through all Sheets in Workbook (From Counter = 1 To Workbook.Sheets.Count).
Line #2: Statements
Statements that are executed a number of times equal to the number of Sheets in Workbook.
Macro example to loop through all sheets in workbook with For… Next
The following macro example:
- Loops through each sheet in the workbook where the macro is stored (For iCounter = 1 To ThisWorkbook.Sheets.Count | Next iCounter).
- Displays a message box (MsgBox) with the name (Name) of the current sheet (ThisWorkbook.Sheets(iCounter)).
Sub loopThroughAllSheetsForNext() 'source: https://powerspreadsheets.com/ 'loops through all sheets in this workbook, and displays a message box each sheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to hold loop counter Dim iCounter As Long 'loop through all sheets in this workbook For iCounter = 1 To ThisWorkbook.Sheets.Count 'display message box with name of current sheet MsgBox ThisWorkbook.Sheets(iCounter).Name Next iCounter End Sub
Effects of executing macro example to loop through all sheets in workbook with For… Next
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each sheet (both worksheets and chart sheets) in the workbook.
#14: Loop through all worksheets in workbook with For… Next
VBA code to loop through all worksheets in workbook with For… Next
To loop through all worksheets in a workbook with a For… Next VBA loop, use a macro with the following statement structure:
For Counter = 1 To Workbook.Worksheets.Count Statements Next Counter
Process to loop through all worksheets in workbook with For… Next
To loop through all worksheets in a workbook with a For… Next VBA loop, follow these steps:
- Identify the workbook containing the worksheets (Workbook).
- Identify the Sheets collection representing all worksheets in Workbook (Worksheets).
- Count the number of worksheets in the Sheets collection (Count).
- Execute a set of Statements a number of times equal to the number of worksheets in Workbook (For Counter = 1 To Workbook.Worksheets.Count).
VBA statement explanation
Lines #1 and #3: For Counter = 1 To Workbook.Worksheets.Count | Next Counter
Item: For … To … | Next …
The For… Next statement repeats the statements a number of times equal to the number of worksheets in Workbook.
Item: Counter
Numeric variable used as loop counter. If you explicitly declare Counter, you can usually declare it as of the Long data type.
Item: = 1
Counter's initial value.
Item: Workbook.Worksheets.Count
Counter's end value, which is equal to the number of worksheets in Workbook.
Workbook is a Workbook object representing the Excel workbook containing the worksheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
The Worksheets.Count property returns the number of objects in the Sheets collection returned by the Worksheets property.
Therefore:
- Workbook.Worksheets.Count returns the number of worksheets in Workbook; and
- For… Next loops through all worksheets in Workbook (From Counter = 1 to Workbook.Worksheets.Count).
Line #2: Statements
Statements that are executed a number of times equal to the number of worksheets in Workbook.
Macro example to loop through all worksheets in workbook with For… Next
The following macro example:
- Loops through each worksheet in the workbook where the macro is stored (For iCounter = 1 To ThisWorkbook.Worksheets.Count | Next iCounter).
- Displays a message box (MsgBox) with the name (Name) of the current worksheet (ThisWorkbook.Worksheets(iCounter)).
Sub loopThroughAllWorksheetsForNext() 'source: https://powerspreadsheets.com/ 'loops through all worksheets in this workbook, and displays a message box with each worksheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to hold loop counter Dim iCounter As Long 'loop through all worksheets in this workbook For iCounter = 1 To ThisWorkbook.Worksheets.Count 'display message box with name of current worksheet MsgBox ThisWorkbook.Worksheets(iCounter).Name Next iCounter End Sub
Effects of executing macro example to loop through all worksheets in workbook with For… Next
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each worksheet in the workbook.
#15: Loop through all sheets in reverse order
VBA code to loop through all sheets in reverse order
To loop through all sheets in a workbook in reverse order with VBA, use a macro with the following statement structure:
For Counter = Workbook.Sheets.Count To 1 Step -1 Statements Next Counter
Process to loop through all sheets in reverse order
To loop through all sheets in a workbook in reverse order with VBA, follow these steps:
- Identify the workbook containing the sheets (Workbook).
- Identify the Sheets collection representing all sheets in Workbook (Sheets).
- Count the number of sheets in the Sheets collection (Count).
- Execute a set of Statements a number of times equal to the number of Sheets in Workbook while clarifying that the looping occurs in reverse order (For Counter = Workbook.Sheets.Count To 1 Step -1).
VBA statement explanation
Lines #1 and #3: For Counter = Workbook.Sheets.Count To 1 Step -1 | Next Counter
Item: For … To …. | Next …
The For… Next statement repeats the statements a number of times equal to the number of worksheets in Workbook.
Item: Counter
Numeric variable used as loop counter. If you explicitly declare Counter, you can usually declare it as of the Long data type.
Item: = Workbook.Sheets.Count
Counter's initial value, which is equal to the number of Sheets in Workbook.
Workbook is a Workbook object representing the Excel workbook containing the sheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
- Chart objects, where each Chart object represents an individual chart sheet; or
- Worksheet objects, where each Worksheet object represents an individual worksheet.
The Sheets.Count property returns the number of objects in the Sheets collection. Therefore, Workbook.Sheets.Count returns the number of Sheets in Workbook.
Item: 1
Counter's end value.
Item: Step -1
Amount Counter changes each loop iteration.
When looping through all sheets in reverse order:
- Counter's initial value is equal to the number of Sheets in Workbook (Workbook.Sheets.Count).
- Counter's end value is 1.
- Counter decreases by 1 each iteration.
Therefore, For… Next loops through all Sheets in Workbook in reverse order (From Counter = Workbook.Sheets.Count To 1 Step -1).
Line #2: Statements
Statements that are executed a number of times equal to the number of Sheets in Workbook.
Macro example to loop through all sheets in reverse order
The following macro example:
- Loops through each sheet in the workbook where the macro is stored in reverse order (For iCounter = ThisWorkbook.Sheets.Count To 1 Step -1 | Next iCounter).
- Displays a message box (MsgBox) with the name (Name) of the current sheet (ThisWorkbook.Sheets(iCounter)).
Sub loopThroughAllSheetsBackwards() 'source: https://powerspreadsheets.com/ 'loops through all sheets in this workbook (in reverse order), and displays a message box with each sheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to hold loop counter Dim iCounter As Long 'loop through all sheets in this workbook (in reverse order) For iCounter = ThisWorkbook.Sheets.Count To 1 Step -1 'display message box with name of current sheet MsgBox ThisWorkbook.Sheets(iCounter).Name Next iCounter End Sub
Effects of executing macro example to loop through all sheets in reverse order
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each sheet (both worksheets and chart sheets) in the workbook in reverse order.
#16: Loop through all worksheets in reverse order
VBA code to loop through all worksheets in reverse order
To loop through all worksheets in a workbook in reverse order with VBA, use a macro with the following statement structure:
For Counter = Workbook.Worksheets.Count To 1 Step -1 Statements Next Counter
Process to loop through all worksheets in reverse order
To loop through all worksheets in a workbook in reverse order with VBA, follow these steps:
- Identify the workbook containing the worksheets (Workbook).
- Identify the Sheets collection representing all worksheets in Workbook (Worksheets).
- Count the number of worksheets in the Sheets collection (Count).
- Execute a set of Statements a number of times equal to the number of worksheets in Workbook while clarifying that the looping occurs in reverse order (For Counter = Workbook.Worksheets.Count To 1 Step -1).
VBA statement explanation
Lines #1 and #3: For Counter = ThisWorkbook.Worksheets.Count To 1 Step -1 | Next Counter
Item: For … To … | Next …
The For… Next statement repeats the statements a number of times equal to the number of worksheets in Workbook.
Item: Counter
Numeric variable used as loop counter. If you explicitly declare Counter, you can usually declare it as of the Long data type.
Item: = Workbook.Worksheets.Count
Counter's initial value, which is equal to the number of worksheets in Workbook.
Workbook is a Workbook object representing the Excel workbook containing the worksheets you loop through.
You can usually work with one of the following properties to refer to this Workbook object:
- Application.ActiveWorkbook.
- Application.ThisWorkbook.
- Application.Workbooks.
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
The Worksheets.Count property returns the number of objects in the Sheets collection returned by the Worksheets property. Therefore, Workbook.Worksheets.Count returns the number of worksheets in Workbook.
Item: 1
Counter's end value.
Item: Step -1
Amount Counter changes each loop iteration.
When looping through all worksheets in reverse order:
- Counter's initial value is equal to the number of worksheets in Workbook (Workbook.Worksheets.Count).
- Counter's end value is 1.
- Counter decreases by 1 each iteration.
Therefore, For… Next loops through all worksheets in Workbook in reverse order (From Counter = Workbook.Worksheets.Count To 1 Step -1).
Line #2: Statements
Statements that are executed a number of times equal to the number of worksheets in Workbook.
Macro example to loop through all worksheets in reverse order
The following macro example:
- Loops through each worksheet in the workbook where the macro is stored in reverse order (For iCounter = ThisWorkbook.Worksheets.Count To 1 Step -1 | Next iCounter).
- Displays a message box (MsgBox) with the name (Name) of the current worksheet (ThisWorkbook.Worksheets(iCounter)).
Sub loopThroughAllWorksheetsBackwards() 'source: https://powerspreadsheets.com/ 'loops through all worksheets in this workbook (in reverse order), and displays a message box with each worksheet name 'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/ 'declare variable to hold loop counter Dim iCounter As Long 'loop through all worksheets in this workbook (in reverse order) For iCounter = ThisWorkbook.Worksheets.Count To 1 Step -1 'display message box with name of current worksheet MsgBox ThisWorkbook.Worksheets(iCounter).Name Next iCounter End Sub
Effects of executing macro example to loop through all worksheets in reverse order
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of each worksheet in the workbook in reverse order.
Learn more about working with sheets and worksheets in VBA
You can get immediate free access to the example workbook that accompanies this VBA Tutorial by subscribing to the Power Spreadsheets Newsletter.
Alternatively, you can access all the files that accompany my Tutorials here.
The following Books are referenced in this Excel VBA sheets and worksheets Tutorial:
- Alexander, Michael (2015). Excel Macros for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
- Alexander, Michael and Kusleika, Dick (2016). Excel 2016 Power Programming with VBA. Indianapolis, IN: John Wiley & Sons Inc.
- Jelen, Bill and Syrstad, Tracy (2015). Excel 2016 VBA and Macros. United States of America: Pearson Education, Inc.
- Walkenbach, John (2015). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.