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.
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
Dim p As Integer, q As Integer
p = Worksheets.Count
For q = 1 To p
If ActiveWorkbook.Worksheets(q).Name = “Dakotaland” Then
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
Application.CutCopyMode = False
Watch the Excel VBA training video below for more details: