CODEWITHSUNDEEP
excelvba
Maury Markowitz
Maury Markowitz

Reputation: 9279

Make a Range in VBA that spans sheets?

I am trying to make a Range that spans several sheets, a 3-D Reference as they call it. Here is MS's example of how to do it manually. Well I need to do it in code (no, I can't use a sheet formula). So I did...

Set m1 = Sheets("Sheet1").Cells(1, 1)
Set m2 = Sheets("Sheet2").Cells(3, 1)
Set m3 = Union(m1, m2)

However, this returns the dreaded '1004' error, as does every other syntax I've tried. Then I tried just doing this manually by shift-selecting multiple sheets and using this...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "range address: " & Target.Address
End Sub

And that always returns only the part of the selection on the ActiveSheet.

So, is there any way to make a Range that spans sheets?

Upvotes: 1

Views: 86

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

A Range object can only be a set of cells on a single worksheet. However you can create an array of ranges or a collection of ranges that can span more than one worksheet.

Upvotes: 3

Related Questions