Excel

Loop through all worksheets in the Active workbook.

Ease of Use

Intermediate

Version tested with

2002, 2003 

Submitted by:

Zack Barresse

Description:

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

Discussion:

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. 

Code:

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 448 times.

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