Copy dynamic range based on last row and column with data

[Josu]

Hello, I am trying to copy range based on number of rows and columns with data, starting with A2
So far I just can copy based on number of rows, but in case if i have more columns, my plan is not working
Can you advice please?

Sub CopyTemplate()
Dim ws As Worksheet
Dim wk As Workbook
Set wk = Workbooks("XXX")
Set ws = Worksheets("Test")
ws.Activate
With ThisWorkbook.Worksheets("Test")
Dim lr As Long
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
'   Copy range

Range("A2:E" & lr).Copy

End With

End Sub

 

[Skyybot]

Gets you last Column that Data is in. Counts blanks.

Dim wb as Workbook, sht as Worksheet, lstCol as Long
Set wb = Workbooks("Workbook_Name.xlsx")
Set sht = wb.Worksheets("Sheet_Name")
lstCol = sht.UsedRange.Columns.Count
Debug.Print lstCol

 

[Skyybot]

Range("A2:E" & lr & lstCol).Copy

 

[Skyybot]

@Josu , I'm sorry. I just noticed you're using 2010. I don't think that has the UsedRange property.

 

[Skyybot]

This should work

Dim wb as Workbook, sht as Worksheet, lstCol as Long
Set wb = Workbooks("Workbook_Name.xlsx")
Set sht = wb.Worksheets("Sheet_Name")
lstCol = sht.Columns.End(xlToRight).Column
Debug.Print lstCol

 

[Josu]

This should work

Dim wb as Workbook, sht as Worksheet, lstCol as Long
Set wb = Workbooks("Workbook_Name.xlsx")
Set sht = wb.Worksheets("Sheet_Name")
lstCol = sht.Columns.End(xlToRight).Column
Debug.Print lstCol

Thank you for response
This doesn't work, it give me result of 16384 which doesn't help me
I need to copy range which starts on A2 and can go all the way down and all the way left, this is why I am trying to understand how I can copy based on last used row and last used column

 

[Skyybot]

Try this

Sub CopyTemplate()
Dim ws As Worksheet
Dim wk As Workbook
Set wk = Workbooks("XXX")
Set ws = wk.Worksheets("Test")
ws.Activate
'With ThisWorkbook.Worksheets("Test")
Dim lr As Long
Dim lc As Long
'   Find last row in column A with data
    lr = ws.Rows.End(xlDown).Row
    lc = ws.Columns.End(xlToRight).Column
'   Copy range

Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Copy

'End With

End Sub

 

[Josu]

Try this

Sub CopyTemplate()
Dim ws As Worksheet
Dim wk As Workbook
Set wk = Workbooks("XXX")
Set ws = wk.Worksheets("Test")
ws.Activate
'With ThisWorkbook.Worksheets("Test")
Dim lr As Long
Dim lc As Long
'   Find last row in column A with data
    lr = ws.Rows.End(xlDown).Row
    lc = ws.Columns.End(xlToRight).Column
'   Copy range

Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Copy

'End With

End Sub

Thanks again, this copies 3 rows and all columns
I changed a bit code, now I managed to get things copied how I want

Sub CopyTemplate()
Dim ws As Worksheet
Dim wk As Workbook
Set wk = Workbooks("Total Capped")
Set ws = wk.Worksheets("Data")
ws.Activate
'With ThisWorkbook.Worksheets("Test")
Dim lr As Long
Dim lc As Long
'   Find last row in column A with data
    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Debug.Print lr
'   Find last column in row 2 with data
    lc = ws.Cells(2, Columns.Count).End(xlToLeft).Column
    Debug.Print lc
'   Copy range

Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Copy

'End With

End Sub

 

[Skyybot]

Thanks again, this copies 3 rows and all columns
I changed a bit code, now I managed to get things copied how I want

Sub CopyTemplate()
Dim ws As Worksheet
Dim wk As Workbook
Set wk = Workbooks("Total Capped")
Set ws = wk.Worksheets("Data")
ws.Activate
'With ThisWorkbook.Worksheets("Test")
Dim lr As Long
Dim lc As Long
'   Find last row in column A with data
    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Debug.Print lr
'   Find last column in row 2 with data
    lc = ws.Cells(2, Columns.Count).End(xlToLeft).Column
    Debug.Print lc
'   Copy range

Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Copy

'End With

End Sub

Awesome!