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)
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.
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:
https://www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/
https://www.exceltrainingvideos.com/create-charts-using-excel-vba/
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
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
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…
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
How can i select a the last row in a sheet and move to copy some of its cells to another worksheet in the same file?
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
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.
Can someone help me on this..?
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.
Hello sir,
I need you help.
I want to copy data from 2 different workbook and paste to sheets of master workbook and compare those data line by line and display result .
Please reply
Priya
Hello Sir,
I have written this code after seeing your videos, but then I am getting an error while pasting special as transpose. Please request your help here
Sub fet()
Rem copy data from closed workbook to active workbook
Dim xlApp As Application
Dim xlBook As Workbook
Dim Sh As Object
Set xlApp = CreateObject(“Excel.Application”)
Rem Path source workbook
Set xlBook = xlApp.Workbooks.Open(“C:\WorkArea\Capital Planning\BLR Dev – Revenue Cycle\2019 Capital Plan_CPA.xlsx”)
xlBook.Sheets(1).Range(“A6:A17”).Copy
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlBook = ActiveWorkbook
Set Sh = xlBook.Sheets(“Sheet1”)
Rem Sh.Activate
Range(“A1:M1”).ClearContents
Range(“A1:M1”).Select
Sh.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
So I’m trying to code something that copies an entire row and then pastes it and transposes it in a different column/worksheet, and then copies the next row and transposes it under the last one. Like this:
1 – 2 – 3
4 – 5 – 6
…to….
1
2
3
4
5
6
How can it be done? I’m really stuck here…
I’ve tried this:
Sub SelectAndCopy2()
Dim x As Integer
Application.ScreenUpdating = False
‘ Set numrows = number of rows of data.
NumRows = Range(“A2”, Range(“A2”).End(xlDown)).Rows.Count
‘ Select cell a2.
Range(“A2”).Select
‘ Establish “For” loop to loop “numrows” number of times.
For x = 1 To NumRows
‘ Find and copy in next empty row.
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 20).Select
ActiveSheet.PasteSpecial Transpose:=True
‘ Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
But I just don’t know lol
i want to copy and transpose rows to columns from many workbooks to one worksheet/ please help me