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

11 thoughts on “Copy Paste Multiple Rows of Data from One WorkBook to Another Using Excel VBA

  1. Candace

    I have 7 columns of data in columns A-G (some cells are blank) each record could have 3 to 10 rows of data each record is separated by a blank row and the last row is bold font and has totals. I would like to copy each record of data and create a graph of the data for each record not including the totals in the last row in bold. How do I create a macro to do this as I have over a thousand rows in my worksheet.

    Reply
    1. Dinesh Kumar Takyar Post author

      You could first loop through all your data using a ‘for next’ loop and removing the blank the rows.
      Looping would be from rows 2 to lastrow, assuming you have headers in row one. Using another loop you could now copy the data except the lastrow to another worksheet or workbook. Now using the VBA code to create charts you could achieve your final goal. Once you have done this, you could refine your complete code further so that the process is completed with a single loop.
      These posts might help:
      http://www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/

      http://www.exceltrainingvideos.com/create-charts-using-excel-vba/

      Reply
  2. Anne Widmer

    Hello Dr. Takyar:

    Thank you so much for your Excel VBA training video’s! You have helped me more than any book I’ve perused on the subject.

    I’m currently working on a project that will entail copying data from one workbook to another. And while one of your videos helped me extensively, I’m having a problem capturing all of the data I want to copy. The code I’m using is as follows:

    Private Sub CommandButton1_Click()
    Dim ItemDescription As String
    Dim Quote As String
    Dim Quote_Blank As Workbook

    Worksheets(“QuoteMaster”).Select
    ItemDescription = Range(“A14:A23”)
    Quote = Range(“B1”)

    Set Quote_Blank = Workbooks.Open(“Z:\Production_Control\Invoice_Item_Tracking.xlsm”)
    Worksheets(“ItemTracking”).Range(“A1”).Select
    RowCount = Worksheets(“ItemTracking”).Range(“A1”).CurrentRegion.Rows.Count
    With Worksheets(“ItemTracking”).Range(“A1”)
    .Offset(RowCount, 0) = ItemDescription
    .Offset(RowCount, 1) = Quote

    End With
    Quote_Blank.Save
    End Sub

    I need to include ranges “A14” through “A23”, but can’t seem to get my code to perform this action. Can you please help me?

    Thank you, in advance, for your time and help

    Best Regards,
    Anne Widmer

    Reply
  3. Mike Wrobel

    Hello Dr. Takyar,

    I am stumped and could use your assistance. I’ve used your videos as a guide to the following VBA code. I have a Workbook that contains a compilation of vendor errors with hundreds of rows and 21 columns of data. What I am attempting to do is when this file is opened each day, a module will run to copy specific data that was entered the day before to separate workbook. The code that I am using is as follows:

    Private Sub Workbook_Open()

    Dim LastRow As Integer, i As Integer, erow As Integer

    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow

    If Cells(i, 21).Value = Date -1 And Cells(i, 20) = “Y” Then

    Range(Cells(i, 2), Cells(i, 18)).Select
    Selection.Copy

    Workbooks.Open Filename:=”C:\Users\Mike\Google Drive\SPECIFIC ERRORS.xlsx”
    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

    I am receiving a Run-Time Error 1004; Application-defined or object-defined error. It works all the way through the opening of the SPECIFIC ERRORS file. Although it appears to select the first cell in the first open row but that could be coincidence. I know the Copy portion is working because if I proceed with Ctrl+V the proper data is pasted.

    Any help you could provide would be most beneficial.

    Kind Regards,

    Mike

    Reply
  4. ABDUL MANAF

    I HAVE MORE THAN 500 COMPANIES WORKERS DATA (IN 500COMPANIES AROUND 11400 WORKERS). I HAVE ENTER THE DETAILS OF WORKIERS IN SHEET1 LIKE COLUMN A= ESTABLISHMENT ID COLUMN B= ESTBLISHMENT NAME, C = EMPLOYEE NAME D=LABOUR CARD NO., E=EMPLOYEE CODE, F= ACCOUNT NO., G= NATIONALITY. NOW I NEED TO PULL THE DATA TO SHEET 2. WHILE IAM ENTERING THE ESTABLISHMENT ID IN SHEET 2 COLUMN A1 OTHER DATA SHOULD COME TO LIKE FROM COLUMN A2= ESTABLISHMENT ID COLUMN B2= ESTBLISHMENT NAME, C2 = EMPLOYEE NAME D2=LABOUR CARD NO., E2=EMPLOYEE CODE, F2= ACCOUNT NO., G2= NATIONALITY. …NEXT EMPLOYEE DETAILS A3..B3..C3……LIKE THAT…

    Reply
  5. Ravi

    Hi,
    I want to copy a single row and its respective heading and put it in a mail in form of table .
    Can someone please help me.

    Regards,
    Ravi

    Reply
  6. Brett Buss

    Hello Dr. Takyar,
    .I have looked this over and I have a very similar issue. I have one Master List of names and ID’s in one spreadsheet. What I would like to do is COPY the whole sheet (for simplicity sake) and then open up multiple spreadsheet files that are in the same folder and paste / overwrite what is in one of the sheets with the data from the Master list. I have over 200 spreadsheets. I know this can be done, This training is so similar, but I am not super good with VBA, and would greatly appreciate any and all help.

    Best regards,

    Brett

    Reply
  7. Nitin Bilare

    Hi Dr. Takyar,

    Hope you are doing fine !

    I wanted you assistance in the issue I’m facing currently.
    I have 2 sheets, (A) Master Data Sheet (B) Detailed Activities.
    Master Data sheet will have all the process name and there corresponding activities along with some other details in other column. (Basically the Database)
    I have 4 columms in Master Data Sheet namely Process name, Activity, Activity Owner and Hours.
    In the Detailed Activities sheet there are multiple coloumns including the columns present in the Master Data Sheet. (i.e. Process name, Activity, Activity Owner and Hours.)

    Issue:

    When I enter a process name in the Detailed Activities Sheet in “Process Name”, I want to autopopulate the corresponding Activity, Activity Owner and Hours column in the Scheduler Sheet.
    Then when a enter another process name below the previos process name entered the corresponding columns should get auto populated.(As per the details mentioned in the Master Data Sheet).

    I made this code but I have given specific range. The following code is only for 1 process name. (Thats why i gave specific range)
    I want to code for atleast 40 processes and I should be able to enter one range below the other.

    For Detailed Activity Sheet
    Column C = Process name
    Column L = Activity
    Column M = Activity Owner
    Column N = Hours

    For Master Data Sheet
    Column A = Process name
    Column B = Activity
    Column C = Acivity Owner
    Column D = Hours

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range(“C2”).Value

    Case “Transfer”

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = Sheets(“Master Data Sheet”).Range(“B2:B12”).Value
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = Sheets(“Master Data Sheet”).Range(“C2:C12”).Value
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = Sheets(“Master Data Sheet”).Range(“D2:D12”).Value
    Sheets(“Detailed Activities”).Range(“D2:D12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“E2:E12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“I2:I12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“J2:J12”).Value = “-”

    Case ” ”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case “”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case Else

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = “Please enter a valid Process Name to update the Activities”
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = “Please enter a valid Process Name to update the Activity Owner”
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = “Please enter a valid Process Name to calculate estimated HRS”

    End Select
    Application.EnableEvents = True
    End Sub

    For transfer process, there are 10 rows of Acitvity, Activity owner and hours.
    So when i enter ” Transfer” in the process column in the Detailed activities sheet, the code should search the keyword “transfer” in the master data shet and then copy the corresponding number of rows of Activity, activity owner and hrs from master data sheet to detailed activities sheet.
    Then when I enter another process name the same function should be carried out again.

    Please advise.

    Kind Regards,
    Nitin.

    Reply
  8. Nasir Qadeer

    Bro:
    try this function in the Target Worksheet

    =INDEX(‘[Source Workbook.xlsx]Sheet1’!A1:A15, COLUMN()-COLUMN(A$1)+1)

    This function is simple and will copy range from a Column in one Worksheet to another Worksheet

    By changing word ‘COLUMN” to ROW, the formula help to Transpose Rows into Column within the Target Sheet.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *