Excel VBA Loop through sheets from left to right

Murray Brown
Murray Brown used Ask the Experts™

I want to use Excel VBA code to loop through the sheets in  a workbook in visible order from left to right.
I'd like this to work even after I have shuffled and added in extra sheets.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Try this.
Dim ws As Worksheet
Dim I As Long
    For I = 1 To ActiveWorkbook.Sheets.Count
        Set ws = ActiveWorkbook.Sheets(I)

        ' do stuff with ws

    Next I

Open in new window

Hi Murray,

This code will do that (change the 'Debug.Print' to whatever you want to do with the sheets):

Option Explicit

Sub ZZ_DeleteMe()

Dim ws As Worksheet
Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
    Debug.Print ThisWorkbook.Worksheets(i).Name

Next i

End Sub

Open in new window

Place the code in a module of the workbook that you want it to apply to, else change the ThisWorkbook reference to point to whatever other workbook you want it to apply to.

Murray BrownHead Developer


Thanks. Didn't realize that it was that simple.
Ryan ChongSoftware Team Lead

or a for each loop like:

For Each ws In Sheets
        ' your stuffs

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial