vba range define

[katto01]

Hello

I am trying to define a range using the sheets assignment like below

Public Sub TEST()
sh = Sheet1.Cells(4, 3).Value
Set rngChart = Sheets(sh).Range(Cells(8, 1), Cells(209, 44))
End Sub

I get an error. what am I missing?

Thanks

[AB33]

Option Explicit

Public Sub TEST()
Dim sh, rngChart As Range
sh = Sheet1.Cells(4, 3).Value
Set rngChart = Sheets(sh).Range(Cells(8, 1), Cells(209, 44))
End Sub

[katto01]

thank you, I guess I messed that

[WasWodge]

Should not the "Cells" have the sheet defined?

Public Sub TEST()
Dim sh, rngChart As Range
sh = Sheet1.Cells(4, 3).Value
Set rngChart = Sheets(sh).Range(Sheets(sh).Cells(8, 1), Sheets(sh).Cells(209, 44))
End Sub

or

Public Sub TEST()
    Dim sh, rngChart As Range
    sh = Sheet1.Cells(4, 3).Value
    With Sheets(sh)
        Set rngChart = .Range(.Cells(8, 1), .Cells(209, 44))
    End With
End Sub

[WasWodge]

Btw, this appears to have been cross-posted here