Thread: Copy paste between workbooks.

Please read the forum rules regarding code tags - all code you post must be enclosed in code tags - see the rules here.
You need to loop through the range and paste the values into the next blank cells

Sub FACSub1()
ActiveSheet.Range("N2:Y2").Copy
ChDir "\\SERVER\Shared\Operation Production\z Reports"
Workbooks.Open Filename:= _
"\\SERVER\Shared\Operation Production\z Reports\FAC Sub-Assembly Batch Summary.xls"
Sheets("Summary information").Range("A2").Select
While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Wend
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Close True ' this will save and close the workbook in one statement
End Sub

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

But I have been told that I should place the selection copy command after the "wend" and not at the beginning.

The wend is the end of the loop in the previous post and the paste command needs to be here as you loop through cells until you find the next cell that is empty.
See edited code - misunderstood - sorry. If you want to paste the values into the last free row in Column A then you could use

Sub FACSub1()
ActiveSheet.Range("N2:Y2").Copy
ChDir "\\SERVER\Shared\Operation Production\z Reports"
Workbooks.Open Filename:= _
"\\SERVER\Shared\Operation Production\z Reports\FAC Sub-Assembly Batch Summary.xls"
Sheets("Summary information").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Close True
End Sub

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”