New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Loop Through All Worksheets

Free Excel Macros

Loop Through All Worksheets

Postby Sisyphus » Fri Feb 10, 2012 8:23 am

This little procedure loops through all worksheets in the current workbook and performs a certain action - either extracting data or writing them - on all except the active sheet.
Code: Select all
Private Sub LoopSheets()

    Dim Ws As Worksheet
   
    With ThisWorkbook
        For Each Ws In .Worksheets
            If Ws.Name <> ActiveSheet.Name Then
                Debug.Print Ws.Name
                ' Place your code here to either
                ' extra data or deposit them
            End If
        Next Ws
    End With
End Sub

Because the ActiveSheet is singled out this procedure can be at the center of a project to write data from a master to subsidiary sheets or from subsidiary sheets to a master.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 25 guests