CODEWITHSUNDEEP
vbaexcel
John63
John63

Reputation: 23

Loop through selected Excel worksheets

In a single Excel workbook I want to select various worksheets to run a VBA subroutine on. I found code that shows how to loop through selected worksheets and it uses "MsgBox sh.Name"; however when I place my code within, it only affects the last sheet selected.

For example, if I select Sheet1, Sheet2, and Sheet3 (in that order), it will only make changes to Sheet3. Below is a simplified example of my code. I can only get the last sheet selected to have "123" assigned to cell A1.

Sub SimplifiedExample()

    Dim sh As Worksheet
    Dim selectedshs As Object
    Set selectedshs = ActiveWindow.SelectedSheets
    For Each sh In selectedshs
        'MsgBox sh.Name
         Range("A1") = 123
    Next sh

End Sub

Below is the code where I'm trying to loop through. It's adding row(s) to a list of data if it passes a test. The code works for one sheet at a time.

Sub LoopingWorksheets()

    Dim sh As Worksheet
    Dim selectedshs As Object

    Set selectedshs = ActiveWindow.SelectedSheets

    For Each sh In selectedshs

        Do While Application.WorksheetFunction.Max(Range("A:A")) < Range("Z2")

            ActiveSheet.Range("A50000").Select
            Selection.End(xlUp).Select
            Selection.EntireRow.Insert
            ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
            Selection.Copy
            ActiveCell.Offset(1, 0).Range("A1:A2").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Range("A1").Select

        Loop

    Next sh

End Sub

Upvotes: 1

Views: 2274

Answers (1)

user4039065
user4039065

Reputation:

You are defaulting the Range object to the active worksheet (which I infer is the 'last sheet' you refer to).

Dim sh As Worksheet
Dim selectedshs As Object
Set selectedshs = ActiveWindow.SelectedSheets
For Each sh In selectedshs
    with sh
        debug.print .Name
        .Range("A1") = 123
    end with
Next sh

if you are going to cycle through the ActiveWindow.SelectedSheets then you might as well actually use each sh as you cycle through them.

Upvotes: 3

Related Questions