Using a named range (columns & rows, but no sheet) and later indicating which sheet to use

Question

Question: I have data on Sheet2 and Sheet3 in my workbook. They have identical property locations listed in columns A and B, but the data in the columns next to them are different so I've defined different ranges. Depending on which report tab I'm trying to populate, I need the code to refer to Sheet2.Range(named range) or Sheet3.Range(named range), but I'm getting errors. It's always selecting the data from Sheet1 or giving me errors. I tried using With End With for the occupancy tab section when defining the named range to indicate the ranges should use Sheet2 but that didn't work. I also tried activating Sheet2 when I move to the "Occupancy Tab Copy and Paste" section below, but that didn't work either. Help!

My Code Currently:

`Dim Pltf As Range
Set Pltf = Sheet1.Range("C3")
Dim message As String

'Define individual regions within platforms

Dim OntarioWest1, OntarioWest2, OntarioWest3, OntarioWest4, OntarioWest9, OntarioWestTransReg As Range
Dim OntarioEast5, OntarioEast6, OntarioEast7, OntarioEast8, OntarioEastTransReg As Range
Dim West1, West2, West3, West4, WestTransReg As Range
Dim Quebec1, Quebec2, Quebec3, Quebec4, QuebecTransReg As Range

'Define the area to select for Ontario West regions

Set OntarioWest1 = Range(("A18:B18"), Range("A18:B18").End(xlDown))
Set OntarioWest2 = Range(OntarioWest1.End(xlDown).End(xlDown).Resize(1, 2), OntarioWest1.End(xlDown).End(xlDown).End(xlDown))
Set OntarioWest3 = Range(OntarioWest2.End(xlDown).End(xlDown).Resize(1, 2), OntarioWest2.End(xlDown).End(xlDown).End(xlDown))
Set OntarioWest4 = Range(OntarioWest3.End(xlDown).End(xlDown).Resize(1, 2), OntarioWest3.End(xlDown).End(xlDown).End(xlDown))
Set OntarioWest9 = Range(OntarioWest4.End(xlDown).End(xlDown).Resize(1, 2), OntarioWest4.End(xlDown).End(xlDown).End(xlDown))
Set OntarioWestTransReg = Range(OntarioWest9.End(xlDown).End(xlDown).Resize(1, 2), OntarioWest9.End(xlDown).End(xlDown).End(xlDown))

'Define the area to select for Ontario East regions

Set OntarioEast5 = Range(OntarioWestTransReg.End(xlDown).End(xlDown).Resize(1, 2), OntarioWestTransReg.End(xlDown).End(xlDown).End(xlDown))
Set OntarioEast6 = Range(OntarioEast5.End(xlDown).End(xlDown).Resize(1, 2), OntarioEast5.End(xlDown).End(xlDown).End(xlDown))
Set OntarioEast7 = Range(OntarioEast6.End(xlDown).End(xlDown).Resize(1, 2), OntarioEast6.End(xlDown).End(xlDown).End(xlDown))
Set OntarioEast8 = Range(OntarioEast7.End(xlDown).End(xlDown).Resize(1, 2), OntarioEast7.End(xlDown).End(xlDown).End(xlDown))
Set OntarioEastTransReg = Range(OntarioEast8.End(xlDown).End(xlDown).Resize(1, 2), OntarioEast8.End(xlDown).End(xlDown).End(xlDown))

'Define the area to select for West

Set West1 = Range(OntarioEastTransReg.End(xlDown).End(xlDown).End(xlDown).End(xlDown).Resize(1, 2), OntarioEastTransReg.End(xlDown).End(xlDown).End(xlDown).End(xlDown).End(xlDown))
Set West2 = Range(West1.End(xlDown).End(xlDown).Resize(1, 2), West1.End(xlDown).End(xlDown).End(xlDown))
Set West3 = Range(West2.End(xlDown).End(xlDown).Resize(1, 2), West2.End(xlDown).End(xlDown).End(xlDown))
Set West4 = Range(West3.End(xlDown).End(xlDown).Resize(1, 2), West3.End(xlDown).End(xlDown).End(xlDown))
Set WestTransReg = Range(West4.End(xlDown).End(xlDown).Resize(1, 2), West4.End(xlDown).End(xlDown).End(xlDown))


'Define the area to select for Quebec

Set Quebec1 = Range(WestTransReg.End(xlDown).End(xlDown).End(xlDown).End(xlDown).Resize(1, 2), WestTransReg.End(xlDown).End(xlDown).End(xlDown).End(xlDown).End(xlDown))
Set Quebec2 = Range(Quebec1.End(xlDown).End(xlDown).Resize(1, 2), Quebec1.End(xlDown).End(xlDown).End(xlDown))
Set Quebec3 = Range(Quebec2.End(xlDown).End(xlDown).Resize(1, 2), Quebec2.End(xlDown).End(xlDown).End(xlDown))
Set Quebec4 = Range(Quebec3.End(xlDown).End(xlDown).Resize(1, 2), Quebec3.End(xlDown).End(xlDown).End(xlDown))
Set QuebecTransReg = Range(Quebec4.End(xlDown).End(xlDown).Resize(1, 2), Quebec4.End(xlDown).End(xlDown).End(xlDown))

'Define platform by using individual regional ranges

Dim OnW, OnE, Wst, Qc As Range
Set OnW = Range(OntarioWest1, OntarioWestTransReg)
Set OnE = Range(OntarioEast5, OntarioEastTransReg)
Set Wst = Range(West1, WestTransReg)
Set Qc = Range(Quebec1, QuebecTransReg)

**'Occupancy Tab:** Uses platform ranges and regional ranges to define cells to copy. **This needs to refer to Sheet2!!**

Dim OntarioWestOcc, OntarioEastOcc, WestOcc, QuebecOcc As Range
Set OntarioWestOcc = Union(OnW, Range(OntarioWest1, OntarioWestTransReg).Offset(0, 2).Resize(, 15))
Set OntarioEastOcc = Union(OnE, Range(OntarioEast5, OntarioEastTransReg).Offset(0, 2).Resize(, 15))
Set WestOcc = Union(Wst, Range(West1, WestTransReg).Offset(0, 2).Resize(, 15))
Set QuebecOcc = Union(Qc, Range(Quebec1, QuebecTransReg).Offset(0, 2).Resize(, 15))

'   Copy and paste data from relevant areas to its respective report tab

**'Occupancy tab copy & paste**

Sheet2.Activate 

If Pltf = "Ontario West" Then
OntarioWestOcc.Copy

ElseIf Pltf = "Ontario East" Then
OntarioEastOcc.Copy

ElseIf Pltf = "West" Then
WestOcc.Copy

ElseIf Pltf = "Quebec" Then
QuebecOcc.Copy

Else
message = "No Platform Selected"

End If

With Sheet4.Range("A6")
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteValuesAndNumberFormats

End With

Sheet4.Activate
Sheet4.Cells(1, 1).Select

A.S.H A.S.H · Accepted Answer · 2017-01-17T18:17:22

You can use the .address property of a Range object in order to use its address and apply it to another sheet.

For example:

Dim r1 As Range, r2 As Range, r3 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("C3")
Set r3 = Sheet2.Range(r1.Address) ' <-- will get you A1 of sheet2
Set r3 = Sheet2.Range(r1.Address, r2.Address) ' <-- will get you A1:C3 of sheet2

Using a named range (columns & rows, but no sheet) and later indicating which sheet to use

1 Answers