Copy a variable Range from one sheer to another in VBA

2
votes

I am trying to estimate the range of a table in an excel spreasheet and then copy it from one sheet to another. I have succcessfully estimated the LastRow and LastColumn of the table using the below code.

    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets("Sheet1")

    'Ctrl + Shift + End
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

How do I copy this range now and paste it to another sheet ? The starting cell is also an input stored as a string.

Any help is appreciated. Thanks !

1
excelrangecopy-pastevba

1 Answers

2
votes

Try this (Untested)

sht.Range("A1:" & Split(sht.Cells(, LastColumn).Address, "$")(1) & LastRow).Copy <Dest>

<Dest> could be something like Sheet2.Range("A1")

To understand what Split(sht.Cells(, LastColumn).Address, "$")(1) does, see the section Column Number to Column Name