How to copy paste or transfer multiple rows of data from one workbook to another using Excel VBA:

Dear Dr Takyar,

I recently came across your website and it has been extremely helpful for me and the business I work for. Thank you!

I having been using this training video: http://www.youtube.com/watch?v=lyNwuXrUAoM&feature=c4-overview&list=UU-vzNYU9x8IYPk_r89mGvXA
in order to create a spreadsheet process for our business to manage our clients and the time that we devote to each client.

I was able to get the data to copy to the master worksheet, however it’s only copying one row. It won’t copy additional rows. I was wondering if you would be able to help me with what I am doing incorrect.

Thanks for your help! I’ve attached my time-sheet and also the master time-sheet. We have other staff who will have time-sheets and I’d like to have each person’s time automatically transfer to the master worksheet.

Missy Quittem
Alternative HRD

The algorithm:
The process is outlined below and the VBA code follows the outline:
1. Use a loop to select the appropriate data
2. Copy the selected data
3. Open the other workbook
4. Select the right worksheet again using a looping process
5. Then find the next blank row for pasting the copied data
6. Paste the data
7. Save the active workbook
8. Close the active workbook
9. Finish the looping process

The complete VBA code attached to a command button:

Private Sub CommandButton1_Click()
LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).row

For i = 4 To LastRow

If Cells(i, 2) = “Dakotaland” Then
Range(Cells(i, 1), Cells(i, 4)).Select
Selection.Copy

Workbooks.Open FileName:=”C:\Users\takyar\Desktop\ClientMasterTimeTracking.xlsx”

Dim p As Integer, q As Integer

p = Worksheets.Count

For q = 1 To p

If ActiveWorkbook.Worksheets(q).Name = “Dakotaland” Then
Worksheets(“Dakotaland”).Select
End If

Next q

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i

End Sub

Watch the Excel VBA training video below for more details:

Further reading:
Macro to Loop Through All Worksheets in a Workbook
Range.Cells Property (Excel)