macro copy a table

[kelvin_9]

Hi All,

I have a table on sheet1 A1:B10, I'm wondering how can i use a macro to copy it to last row on sheet2?
I have this but it returns code 1004 error, application defined or object defined error

Sub test()

Sheets("Sheet1").Select
Range("A1:B10").Select
Selection.Copy

Sheets("Sheet2").Select
Cells(Rows.Count, 1).End(xlUp).Offset(6, 1).Select
ActiveSheet.Paste

End Sub

thanks for your guidance

 

[alansidman]

Option Explicit

Sub test()

Sheets("Sheet1").Select
Range("A1:B10").Select
Selection.Copy

Sheets("Sheet2").Select
Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1).Select
ActiveSheet.Paste

End Sub

Shorter and more succint

Option Explicit

Sub test()

Sheets("Sheet1").Range("A1:B10").Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)

End Sub

 

[kelvin_9]

Option Explicit

Sub test()

Sheets("Sheet1").Select
Range("A1:B10").Select
Selection.Copy

Sheets("Sheet2").Select
Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1).Select
ActiveSheet.Paste

End Sub

Shorter and more succint

Option Explicit

Sub test()

Sheets("Sheet1").Range("A1:B10").Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)

End Sub

excellent alan, thanks so much
last but not least, do you think i can adjust this table row size to 30 after run this macro too?

 

[alansidman]

How about this?

Option Explicit

Sub test()
'Define last row as long integer
Dim lr As Long
'Determine last row of data in column A
lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Copy Range A1 to B and last row and paste to Sheet2
Sheets("Sheet1").Range("A1:B" & lr).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)

End Sub

 

[kelvin_9]

How about this?

Option Explicit

Sub test()
'Define last row as long integer
Dim lr As Long
'Determine last row of data in column A
lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Copy Range A1 to B and last row and paste to Sheet2
Sheets("Sheet1").Range("A1:B" & lr).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)

End Sub

Nope, I've also upload my workbook here,
ps. I set row height to 50

http://kel.ddns.net/f/3b89f7beb0/?raw=1

 

[alansidman]

I misundetstood. I thought you were looking for the last row in a range. Actually, you were referring to row height. Will look and recode.

EDIT: Your link opens back to this same MrE page.

 

[alansidman]

Sub test()
Sheets("Sheet1").Range("A1:B10").Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)
Sheets("Sheet2").Rows("7:16").RowHeight = 50
End Sub

 

[kelvin_9]

Sub test()
Sheets("Sheet1").Range("A1:B10").Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Rows.Offset(6, 1)
Sheets("Sheet2").Rows("7:16").RowHeight = 50
End Sub

not your fault and it's my bad presentation

however this height is only for row 7:16?
can it be flexible on any row after offset 6 more rows?
for instance, last row on 18, table height at row 24

thanks for the guidance