Ubound Error

[Ben AFF]

Hi, Im getting an error when I try to run a Ubound Sub example found in this link https://www.wallstreetmojo.com/vba-ubound/

Please can you help me understand what is wrong/missing in the code? Thank you.

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
    DataRange = Range("A2", Range("A1").End(xlDown).End(xlToRight))   
    Worksheets.Add   
    Range(ActiveCell, ActiveCell.Offset(UBound(DataRange, 1) - 1, UBound(DataRange, 2) - 1)) = DataRange  
    Erase DataRange
End Sub

 

[Fluff]

What error do you get & on which line?

 

[Ben AFF]

Thanks Fluff.

The error is in this line

Range(ActiveCell, ActiveCell.Offset(UBound(DataRange, 1) - 1, UBound(DataRange, 2) - 1)) = DataRange

Application-defined or object-defined error.

 

[RoryA]

Which module is that code in?

 

[Ben AFF]

In running the code in the worksheet window code.

 

[Ben AFF]

Sorry I see now, I was not running it in a module.

 

[RoryA]

In a worksheet code module that first Range property refers to the sheet the code is in. Since the Activecell is not on the same sheet, you get an error trying to refer to a range on one sheet using cells on a different sheet. If you'd used Activesheet.Range it would have been fine.

 

[Ben AFF]

Thank you Rory, its clear now.

 

[RoryA]

Glad we could help.