Error when using trying to add a range to a variable

[Kevali]

Every time I use the set command to point to a range of cells ready for me to load an array into them I get 1004 error code.

Any Ideas.

Snipit of code
Dim Reportrange as Range

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Name = reportname
Worksheets(reportname).Select
Range("A1").Select
Set ReportRange = worksheets(reportname).Range(Range(Cells(UBound(SickReport, 2), 1), Cells(1, 6))

I get error code 1004?

 

[VoG]

Try like this

Dim Reportrange As Range

Sheets.Add After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Name = "reportname"
    Set Reportrange = .Range(.Cells(UBound(SickReport, 2), 1), .Cells(1, 6))
End With

 

[MrKowz]

Try:

Dim Reportrange As Range
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = reportname
With Sheets(reportname)
    Set Reportrange = .Range(.Cells(UBound(SickReport, 2), 1), .Cells(1, 6))
End With

You had an extra Range() in there, also, you need to qualify each of the Cells() reference with the sheet name

 

[Kevali]

The code now works, I think the extra range crept in as I was trying al sorts of range options to refer to except qualifying the sheet ref as well.

Thanks Guys.

 

[pgc01]

Hi

Also:

Dim Reportrange As Range
 
With Sheets.Add(After:=Sheets(Sheets.Count))
    .Name = "reportname"
    Set Reportrange = .Range(.Cells(UBound(SickReport, 2), 1), .Cells(1, 6))
End With