Setting a range using sheets and cells

[288enzo]

Good morning,

I'm stumped what I'm doing wrong.

I know the following work and give the same result.

Set rng1 = Range("A5:A11")
Set rng2 = Range(Cells(5, 1), Cells(11, 1))

The problem I'm having is when I add in the name of a worksheet.

Set rng1 = Sheets("Continuous Learning Details").Range("A5:A11") 'This works perfectly
Set rng2 = Sheets("Continuous Learning Details").Range(Cells(5, 1), Cells(11, 1)) 'This gives me an error code 1004, Application-defined or object-defined error

 

[Fluff]

You need to qualify all ranges (including the Cells) with the worksheet like

Set rng2 = Sheets("Continuous Learning Details").Range(Sheets("Continuous Learning Details").Cells(5, 1), Sheets("Continuous Learning Details").Cells(11, 1))

or shorter

With Sheets("Continuous Learning Details")
   Set rng2 = .Range(.Cells(5, 1), .Cells(11, 1))
End With

 

[288enzo]

You need to qualify all ranges (including the Cells) with the worksheet like

Set rng2 = Sheets("Continuous Learning Details").Range(Sheets("Continuous Learning Details").Cells(5, 1), Sheets("Continuous Learning Details").Cells(11, 1))

or shorter

With Sheets("Continuous Learning Details")
   Set rng2 = .Range(.Cells(5, 1), .Cells(11, 1))
End With

That makes sense, thank you again. You're always so helpful.

 

[Fluff]

Glad to help & thanks for the feedback.