Looping through every worksheet or every workbook is a very common action. One which you may be doing on a regular basis. The code below should set you on the right path.
Loop through every worksheet
Sub LoopThroughWorksheets() 'Create a variable to hold the worksheet and also the output message Dim Ws As Worksheet Dim Message As String 'Loop through each worksheet in the Worksheets collection of the active workbook For Each Ws In ActiveWorkbook.Worksheets 'Insert the actions you wish to take on every worksheet 'In this example adding the name of a sheet to a string Message = Message & Ws.Name & vbNewLine Next Ws 'Output the string containing the name of each worksheet MsgBox Message End Sub
Loop through every workbook
Sub LoopThroughWorkbooks() 'Create a variable to hold the workbook and also the output message Dim Wb As Workbook Dim Message As String 'Loop through every open workbook in the workbooks collection of the active Excel session For Each Wb In Workbooks 'Insert the actions you wish to take on every workbook 'In this example adding the name of a sheet to a string Message = Message & Wb.Name & vbNewLine Next Wb 'Output the string containing the name of each workbook MsgBox Message End Sub
Discover how you can automate your work with our Excel courses and tools.
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.