How to declare Excel range in VBA?

Question

So I lost all of my VBA notes and am having issues redoing some logic here so I apologize if this looks very basic. I am trying to find set the top and find the last populated cell in a column and set them both in my range to be able to copy and paste it into a log. When I am getting to the MYRANGE part, I am getting:

error 1004

I've tried using a more basic selection to achieve what I'm trying to do, but the raw data is never going to be the same size so I wanted to come back to this to try and see if I could get help.

Sub CopyRows()

    Dim sht As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim MYRANGE As Range
    Dim Top, Bottom, Left, Right As Long        

    Set sht = Worksheets("Data")
    sht.Activate

    Set Top = Range("B2")
    LastRow = Cells(Rows.Count, "B").End(xlUp).Select
    Set MYRANGE = Range("Top", "LastRow").Select.Copy

End Sub

As I mentioned, I am trying to set my range as the "Top" and "Bottom" or "LastRow" so that way I don't have to worry about the actual size of the data and copy and paste it over to the other worksheet.

Answer 1

When setting a range you do not use .Select or .Copy

When using variable ranges in another range you do not use ""

And do not use .Activate or .Select

Sub CopyRows()
    Dim sht As Worksheet
    Dim LastRow As Range
    Dim LastColumn As Long
    Dim MYRANGE As Range
    Dim Top As Range, Bottom As Range, Left As Range, Right As Range

    Set sht = Worksheets("Data")

    Set Top = sht.Range("B2")
    Set LastRow = sht.Cells(Rows.Count, "B").End(xlUp)
    Set MYRANGE = sht.Range(Top, LastRow)
    'Now you can do something with MYRANGE like MYRANGE.Copy
End Sub