Reference multiple sheets in one line

Question

I was wondering if there is a way to reference multiple sheets in a single line in Excel VBA. I know you are able to reference multiple ranges, columns, rows via:

Range("G1:G5, G6:G10, H5:H10") etc...

I want to be able to do the same thing with sheets I tried this:

Sheets("Sheet1_Name, Sheet2_Name, Sheet3_Name").Range(...)

But that didn’t work so I was wondering if there was a special way to do this aside from putting the sheet names into variables then referencing the variables.

Daniel Dušek · Accepted Answer · 2013-07-19T12:34:30.087

3

Sub Test()
    Dim sheetsArray As Sheets
    Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

    Dim target As Range
    Dim sheetObject As Worksheet

    ' change value of range 'a1' on each sheet from sheetsArray
    For Each sheetObject In sheetsArray
        Set target = sheetObject.Range("A1")
        target.Value = "Test"
    Next sheetObject
End Sub

edited Jul 19 '13 at 12:34

answered Jul 19 '13 at 12:19

Daniel Dušek

13,683
5
36
51

so then I would just reference a the array when im changing the same range in every sheet in that array? ie sheetsArray.Range(...) = blah blah? – user1305569 Jul 19 '13 at 12:23
Edit: I tried that. I get a compile error pointing to Private Sub Worksheet_Change(ByVal Target As Range) – user1305569 Jul 19 '13 at 12:29
Was going to post a C#/VB.NET solution, but you did it before with VBA. +1 – mike27015 Jul 19 '13 at 12:49

Reference multiple sheets in one line

1 Answers1

Linked