Referring to ranges

Referencing ranges in other sheets

Switching between sheets by activating the needed sheet slows down your code. To avoid this, refer to a sheet that is not active by first referencing the Worksheet object:


This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet.

To reference a range in another workbook, include the Workbook object, the Worksheet object, and then the Range object:


To use the Range property as an argument within another Range property, identify the range fully each time. For example, suppose that Sheet1 is your active sheet and you need to total data from Sheet2:

WorksheetFunction.Sum(Worksheets("Sheet2").Range(Range("A1"), _

This line does not work. Why not? Although Range("A1"), Range("A7") is meant to refer to the sheet at the beginning of the code line (Sheet2), Excel does not assume that you want to carry the Worksheet object reference over to these other Range objects; instead, Excel assumes that they refer to the active sheet, Sheet1. So, what do you do? Well, you could write this:

WorksheetFunction.Sum(Worksheets("Sheet2").Range(Worksheets("Sheet2"). _
 Range("A1"), Worksheets("Sheet2").Range("A7")))

However, not only is this a long line of code, but it is also difficult to read! Thankfully, there is a simpler way, using With...End With:

With Worksheets("Sheet2")
 WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A7")))
End With

Notice now there is a .Range in your code but without the preceding object reference. That’s because With Worksheets("Sheet2") implies that the object of the range is that worksheet. Whenever Excel sees a period without an object reference directly to the left of it, it looks up the code for the closest With statement and uses that as the object reference.