Copy Paste Multiple Rows of Data from One WorkBook to Another Using Excel VBA

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:
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

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
End If

Next q

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

ActiveSheet.Cells(erow, 1).Select
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)