You can use the Range object to refer to cells in multiple worksheets in a workbook. To do this, you need to create a reference to each worksheet by using the Worksheets property of the Workbook object that contains the worksheets. The following example shows how to create a reference to cells A1:A5 on Sheet1 and Sheet2 in the current workbook.

Dim sheet1 As Worksheet
Dim sheet2 As Worksheet
Set sheet1 = ActiveWorkbook.Worksheets("Sheet1")
Set sheet2 = ActiveWorkbook.Worksheets("Sheet2")

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = sheet1.Range("A1:A5")
Set rng2 = sheet2.Range("A1:A5")

You can now use the Union method of the Range object to create a reference that includes both ranges:

Dim rng3 As Range
Set rng3 = Union(rng1, rng2)