Loop through selected Excel worksheets

Question

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

You need to qualify your ranges with period identifiers. No where do you use sh in loop. — Parfait

Unknown Unknown · Accepted Answer · 2017-11-11T21:31:49

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.

Loop through selected Excel worksheets

1 Answers