I recorded this macro, but it does not work. What the macro should do is this: C
ID: 3560418 • Letter: I
Question
I recorded this macro, but it does not work. What the macro should do is this:
Copy named range "New", go to Table 4 on "Summary" tab, enter new row in Table 4 by going to the end of the table and hitting the tab key, then pasting the copied range onto the new row. Instead, when I recorded my steps the macro references a specific range (A10) as the next available row of the table, therefore everything copied/pasted will always go on row 10 instead of the next new row of the table. Any help would be appreciated.
Application.Goto Reference:="New"
Selection.Copy
Sheets("Summary").Select
Range("Table4[[#Headers],[Column10]]").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=True
Range(A10).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A10").Select
End Sub
Explanation / Answer
It shouldn't be necessary to select the ranges but because I do not know exactly what your worksheet looks like then I have retained your code and edited so that it should work irrespective of how many rows you have in the table.
Note all of my comments and in particular that the Copy gets lost when a new row is inserted so the copy line cannot be done until after inserting the new row.
'Selection.Copy 'Copy gets lost when new row is inserted
Sheets("Summary").Select
Range("Table4[[#Headers],[Column10]]").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=True
'Range(A10).Select 'Delete this row
Selection.Offset(1, 0).Select 'Insert this row
Range("New").Copy 'No need to select or goto just copy reference
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Range("A10").Select 'Should not be required