Copying sheets from various workbooks and pasting into a sheet in a different workbook

Question

I have 7 workbooks which are all of an identical format on each sheet. I want to grab "Sheet2" from each of the workbooks and paste them into a master sheet in a separate workbook. I have managed to do this but the pasting keeps overwriting the the last paste. How can I paste below the last pasted entry?

I have this for each of the 7 sheets

Sub MoveHR()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("\\ukkh1-afp-sv1o\Shared\Sales\Field Sales - 

ERN\DSRs\. DSR's February 2016\HRehman February.xlsm")
Set y = Workbooks.Open("\\ukkh1-afp-sv1o\Shared\Sales\Field Sales - ERN\DSRs\Trackers\SIMPosa Tracker.xlsm")

'Now, copy what you want from x:
x.Sheets("SIMPosa").Range("A:J").Copy

'Now, paste to y worksheet:
y.Sheets("Current Month").Range("A1").PasteSpecial xlPasteValues

'Close x:
Application.CutCopyMode = False
x.Close False
y.Close True
End Sub

Slightly offtopic: I would suggest you always write out your commands, even if you technically don't have to. It makes your code easier to read and once you start writing longer code, it might really help you. So I'd suggest to write: x.Close SaveChanges:=False — RobK

RobK RobK · Accepted Answer · 2016-02-02T17:02:50

Try to add a loop to find the next empty row, like this:

dim i as integer
i=1

do until y.sheets("Current Month").cells(i,1).value=""
    i=i+1
loop

y.Sheets("Current Month").Cells(i,1).PasteSpecial xlPasteValues

Copying sheets from various workbooks and pasting into a sheet in a different workbook

2 Answers