I have 3 Excel tables. Table 1 (SummerCompTable) includes all the records found in tables 2 (POYCompTable) and 3 (ScratchCompTable). Table 1 is used to edit the records and has a column that indicates where each record in table 1 belongs. On completion of editing I need to copy edit table records to table 2, 3 or to both.
To get started I created the following macro:
Code:
Sub Macro4()
Range("SummerCompTable").Select 'Editing table
Selection.Range("A1:C1").Select 'Cols to be copied
Selection.Copy
Range("POYCompTable").Select 'Table 2
Selection.ListObject.ListRows.Add (1) 'New line at top of table
Selection.Range("A1:C1").Select 'Tried Range("A1") but paste still fails
Application.ActiveSheet.Paste 'Fails with run time error 1004 - error message "Paste method of Worksheet class failed"
'Selection.PasteSpecial 'This doesn't work either
End Sub
What I can't understand is that if I stop the macro before the activesheet.paste line I can complete the paste manually from the clipboard.
Any suggestions?