Paste to end of table

[spootage]

Hello there!

I have recorded a macro via excel that will copy a string of data from one worksheet and paste to another worksheet of the same book.

At the moment it pastes my data to "A13" however this is a table and I would like it to paste to the end of said table, which will not always be "A13". My table is the only thing on the sheet and goes from A:G.

How can I modify my code below to achieve this?


Sub CopyPaste()

Sheets("Magic").Select
Range("J2:P2").Select
Selection.Copy
Sheets("Data").Select
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Coaching Form").Select

End Sub


Thanks in advance.

 

[My Aswer Is This]

Try this:

Sub Copy_Range()
'Modified 4/4/2022  9:57:41 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Magic").Range("J2:P2").Copy
Sheets("Data").Cells(Lastrow, 1).PasteSpecial
Application.CutCopyMode = False
Sheets("Coaching Form").Select
Application.ScreenUpdating = True
End Sub

 

[spootage]

Try this:

Sub Copy_Range()
'Modified 4/4/2022  9:57:41 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Magic").Range("J2:P2").Copy
Sheets("Data").Cells(Lastrow, 1).PasteSpecial
Application.CutCopyMode = False
Sheets("Coaching Form").Select
Application.ScreenUpdating = True
End Sub

Thank you!

I had to modify your code slightly to paste as values only, so not including the formulas, but everything works now!

 

[My Aswer Is This]

Thank you!

I had to modify your code slightly to paste as values only, so not including the formulas, but everything works now!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.