Copy a variable Range from one sheer to another in VBA

Question

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 !

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

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

edited May 23 '17 at 12:06

Community

1
1

answered Sep 10 '15 at 14:37

Siddharth Rout

147,039
17
206
250

I tired this but I am getting the error Application defined or object defined error. – Amritha Sep 10 '15 at 15:01
This is my code - sht.Range("A1:" & Split(sht.Cells(, LastColumn).Address, "$")(1) & LastRow).Copy (sht2.Range("A1")) – Amritha Sep 10 '15 at 15:02
Have you defined `Sht2`? – Siddharth Rout Sep 10 '15 at 15:02
yes - Dim sht2 As Worksheet Set sht2 = ThisWorkbook.Worksheets("Sheet2") – Amritha Sep 10 '15 at 15:03
Ah I see your mistake... Remove the extra brackets... try this `sht.Range("A1:" & Split(sht.Cells(, LastColumn).Address, "$")(1) & LastRow).Copy sht2.Range("A1")` – Siddharth Rout Sep 10 '15 at 15:07
Great ! Thank you so much :) – Amritha Sep 10 '15 at 15:08

Copy a variable Range from one sheer to another in VBA

1 Answers1