Loop through all worksheets in the Active workbook.

Ease of Use


Version tested with

2002, 2003 

Submitted by:

Zack Barresse


This is a "shell" code. This will loop through all worksheets in the active workbook and perform any code (where specified below). 


Sometimes you may want to do the same thing to every worksheet in your workbook. This is a fast and easy way to do so, and can be a great time saver - especially in large workbooks. 


instructions for use


Option Explicit Sub LoopThroughSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets '** Perform code here ** 'E.g. On Error Resume Next 'Will continue if an error results ws.Range("A1") = ws.Name '*********************** Next ws End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. (Adjust code as necessary.)
  2. From Excel, press Alt + F8 to open the macro dialog box.
  3. Select LoopThroughSheetsand.
  4. Click Run.

Sample File:

LoopThroughSheetsEx.zip 6.49KB 

Approved by mdmackillop

This entry has been viewed 429 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express