|
|
|
|
|
|
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
On Error Resume Next
ws.Range("A1") = ws.Name
Next ws
End Sub
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- (Adjust code as necessary.)
- From Excel, press Alt + F8 to open the macro dialog box.
- Select LoopThroughSheetsand.
- Click Run.
|
Sample File:
|
LoopThroughSheetsEx.zip 6.49KB
|
Approved by mdmackillop
|
This entry has been viewed 448 times.
|
|