Copy row to another workbook

[primagic]

I have the following code which copys the active row and pastes it to the next available row in another worksheet.

ActiveCell.EntireRow.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

How do I modify the code to copy the range A2:AB2 (rather than the active row) to another existing workbook stored in a particular folder.

Thanks

 

[zelgar]

Provided that you want it to be pasted in the same location as the existing line of code:

Range("A2:B2").Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

 

[primagic]

I have modified my code to copy from active workbook to another work, however I am getting the 'subscript out of range error' on the Selection.Copy line.

The target workbook exists and there is an archive worksheet

Range("A2:AB2").Select
     Selection.Copy Destination:=Workbooks("Y:\People & Culture Europe\HR Operations\Customer Support Services\Support Hub\Team Folders\Andrew\LeaverArchive.xlsx").Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[zelgar]

I don't think you can do all of this in one statement. You'll need to open the other spreadsheet first before you can edit it.

 

[SkipVought]

I'd do this...

Dim wbTO as workbook

'open the workbook 
Set wbTO = Workbooks.open("\People & Culture Europe\HR Operations\Customer Support Services\Support Hub\Team Folders\Andrew\LeaverArchive.xlsx")
 
'copy 'n' paste
Range("A2:AB2").Copy Destination:=wbTO.Sheets("Archive").Range("A" & wbTO.Sheets("Archive").Rows.Count).End(xlUp).Offset(1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}