Copy Paste Data from Multiple Rows from one Workbook to Another using Excel VBA
HTML-код
- Опубликовано: 25 ноя 2013
- Our Excel training videos on RUclips cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
For details you can visit our website:
www.exceltrainingvideos.com/c...
A user wishes to copy data from multiple rows one Excel workbook to another using VBA.
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 comple VBA code is available on our website at:
www.exceltrainingvideos.com/c...
For more knowledge read the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
Thank you very much Dr. Takyar, your video helped me a lot!
Respected Sir !
Thank you very much in first, for sharing the wisdom to others.
Thank you once again,
Very useful.
Thank you sir.
Very impressive teaching
Dr. Takyar,
Very educational video. I appreciate the clarity of your explanation and teaching style. Please consider offering a structured VBA course.
George
Appreciate SO MUCH your teaching style, and all your great videos. You are so thoughtful and organized in your step by step methodology. You really have a gift for this !! Can't say thank you enough.
dorianmodify Thank you!
Dr Takyar - many thanks for taking the time to post such informative videos on excel - it is a great thing to share such knowledge !!!
+Keith Keller Thank you!
This code solved our problem.Thank you Dinesh sir
Thanks ....
We look forward for more stuff from you to become xl in VBA aspirant excel users like me......
Hello, thanks for all the nice tutorials you've uploaded. I've found much useful from them.
Was thinking about this one, is it possible to use Autofilter in this and copy all those rows that are filtered in one command. Because now the open-close command is considerably slowing down my workstation.
Thanks!
sir thank you so much
Thanks... Thanks....... Thanks.....
Thanks you very much for this video, it was very helpful. Could you please develop a bit more about the clearing datat before pasting. I try to put : ActiveSheet.ClearContents before pasting but i am still getting an error. Do i have to do another loop? Thanks in advance
Another good example of data passing. Just a quick question if using the same methodology but instead of separate workbooks I just want to use the same workbook but data selection pasted in the different sheet e.g from Sheet 1 to Sheet 2 and Sheet 3. Where do I have to make changes?
thanks
Hii Dr Takyar, I want to know is there any way to collect data from Web after solving the captcha entry.. automatically
one captcha entry = gives one data
Hi Sir. Using Visual Basic Studio Express 2010 or 2012, how can I copy data continuously from an Excel sheet to a form on a web page? Thank you so much for you assistance
Nice vdo sir
Very useful
Thanks for the feedback. Did you share the video with your friends or on social media?
Dear Dr. Takyar,
I am trying to "update" workbook with the help of a button. I want to have in "fileA" a button, that when clicked copies all the info in "tabA" in "fileB".. into "tabA" in "fileA".
So wanr to copy all info in a tab from one file into another. When I give the comand with the help of a button.
How can I do so?
Hello, How can I modify this code to copy just the value of the cells, not formula? Thank you in advanced!!!
Hi Paul,
Am regulery following your tips and tricks when i found critical things. Now I need small help from your end. Recently in our office daily we are getting clients raw files, and we have to modify it to our specific format. For that we are wasting our time. SO that i have to write VBA code, but am failed to write it. Can you please help on this.
Rawdata: Will get nearly 20-23rows and Hundreds of coloumns data from client.
Client Format; we have to convert in this format.
Like this we have more files. SO request you to please provide the code.
Dr Takyar,Thank you for your brilliant videos, they've helped me out a lot! I do have a small issue however.I'm trying to paste the values from one workbook into another workbook with out taking the formula or formatting with it.My problem lies with the final paste, currently it gives a run time error 1004. It will work as just Paste, but I want the values to be pasted only not the formula, formatting etc.What I am doing wrong or is there a better way to achieve the transfer of data for one workbook to another, bearing in mind that one the data file will be called something new each iteration.Many thanks in advance for your time and advice.Neil
Here is my code Private Sub CommandButton4_Click()
'Raw result data is extracted from the CMF WorkSheet (this is within a workbook that will have a unique file name each time it is created) and added to the AV FOA Data WorkSheet within the FOA Results Tracker Workbook.
'Data extract of class results per section/candidate
Range("AE5:AP20").Select
Selection.Copy
'Open FOA result tracker stored on a Shared Server that requires you to check out the document
Dim fil As String
fil = "file location/20160224-FOA_Results_Tracker.xls"
If Application.Workbooks.CanCheckOut(fil) Then
Application.Workbooks.CheckOut fil
Application.Workbooks.Open fil
'need to select Sheet1 (AV FOA Data)and paste values only, into next empty row on the data table
erow = Worksheets("AV FOA Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues
ElseIf MsgBox("File already Checked Out") Then
End If
End Sub
+Neil Kipling Just place an apostrophe before this line and run the code like so:
'ElseIf MsgBox("File already Checked Out") Then
Can this code be altered to search for data that is in a range, for example, to search for data fields that are greater than a certain number and less than another number and paste them into the other workbook? What alterations would be necessary to do this?
Margaret Schalliol This link might help:@rghBdWGSvqo
Hi Sir. How can I copy data continuously from an Excel sheet to a form on a web page? Thank you so much for you assistance
Hi Sir, Please advise how do we copy data from workbook to another based on 2 date ranges. for instance i have a workbook database for a year and i only want the data between April to August only. How do we do that through VBA please. Thanks
nice sir 👍👍👍👍👍👍
Keep watching and sharing the VBA lessons.
sir its really useful tysm for dis....but i ave a problem i want to update my daybook daily but this code update the whole data from start ov the sheet(start ov month):(
What if i want to copy paste for all companies?
Hye Sir,
what if i want copy all the company with the row?
i mean,not only for dakotaland only, but for all company?
thank you Sir
Hello Dr Takyar,
I was wondering if there was a way do just copy the last 5 rows to paste in another workbook?
Use a looping process. This link will guide: www.exceltrainingvideos.com/tag/reverse-for-next-loop/
Hi Dr Takyar.. i was hoping you can help me with my project. It is somehow similar to this video.
I have 2 files, one is the master file that inludes the summary of the report and the raw data which we generate from our tool. I would like to copy the data from the raw data file into the master file but my challenge is this: i have multipile rows and columns with different headers depending on the date. the range may also vary depending on the number of hours we received calls. for example, for this week July 21-25, the support hours is from 9am-6pm, but the scenario may be different next week. It can be that the support hours will be from 10am-7pm or even longer. also, the dates will be different as well like per column it will be July 28-Aug 1. appreciate your help. thanks!
Phil. I was wondering if you ever got a response to this question. My project is very similar to yours.
Hi Dinesh, I love your videos. I need help again. I am making a workbook that when I enter information in it. Example it would be all in a row. A5,B5,C5,D5,E5. and when the last column is entered I would like for that row to be transferred to another workbook on the first blank line and then save that workbook. And so on if I entered info into the next row in Workbook 1, A6,B6,C6,D6,E6. Thank you for all your help.
ChAz Gamr In the first case you wish to copy data from the last row. Once this row is copied, you then go to the relevant workbook, open the relevant worksheet and then find the next blank row for data input. Here you paste the data. You can find the complete description, vba code and a sample file here:
www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Dinesh Kumar Takyar Hi Dinesh Thank you again, The example you gave is looking for the Date cells. How do I remove that and just select the last row entered in "workbook 1"
hi Dr. Taakyar,
i have written somany times to you for a help. i am gettinng a dataa sheet from the client every month. now i have to set the sheet on 4 condition. 1st i have to filter the data on "DD" THEN PASTE IS IN A DIFFERENT SHEET. 2nd is "Direct Debit" and the same procecss 3rd is " Dd" and 4th is " Direct Debit". because the data is not not fixed need a dynaic range. i hav tried so many formulass notnowkrig. aany help on thiss will highly appricicattd.
+debasis mahapatro ruclips.net/video/PIzaXxIwzKM/видео.html
Get all the VBA details here: www.exceltrainingvideos.com/how-to-transfer-data-from-one-sheet-to-another-using-wild-card-characters/
Thanks Dr.,
Is there a way to use Match or Find method in UserForm ?
regards,
***** Watch out for the video on next Thursday. I'll show how to use find and search to avoid duplicate entries during data entry via user-form.
Thanks
***** www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-worksheet-while-transferring-data-via-userform/
Hello Dr TaKyar, I will like to Thank you for your tutorials. They help me a lot on the work I do. I have a question, I'm trying to use this specific code for a similar project I'm working on, however I'm getting a Run-time error '424': Object required. When I Debug it highlights the "erow" line. Could you please help me understand why I'm getting this error message? Thank you so much for your help and for sharing your knowledge.
+Carlos and Ruth Ramos Check the definition of erow. Mostly people make an error at this point: End(xlUP). Instead of l for London many people use 1 (one).
+Dinesh Kumar Takyar thank you so much for the reply. I found my mistake.. could you please advise what changes are needed if I want data for different city to go to the next tab of the destination workbook. thank you for your help!!
+Carlos and Ruth Ramos These links will help: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
+Dinesh Kumar Takyar Sr. thank you for this information. I reviewed the information and tried to write my code using a For Each Loop, to loop trough each worksheet in in the destination workbook where I want the data to be paste, however my code still don't work. what I'm trying to do is exactly as your tutorial but to also paste the data in the other worksheets once the information match.. would you recommend me to use the for each loop? if so, would you say I have to write several loops one for each criteria?? I wrote the loop in several way but unfortunately it doesn't work.. thank you for your kind help!!
+Carlos and Ruth Ramos Best way to find a good solution is to try with one criteria. Next you can try many criteria like shown in this video: www.exceltrainingvideos.com/how-to-transfer-data-from-one-sheet-to-another-using-wild-card-characters/
Hello sir,
These tutorials are very help full while going through this video in third line of code (for loop) I am getting an error ... As type mismatch .. Please help if possible... Appreciate your help ..
The link to this VBA tutorial will help: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Good morning Dinesh. I love this video. It is exactly what I have been looking for.
I am in need of your help.
I am using this code, but I am trying to use other names and put them on other worksheets. Sych as using the same code to find "City of Brandon" and paste it on the "City of Brandon" worksheet. The problem is it will only paste it on the original sheet. In the case of the video that is "Dakotaland".
To sum it up my code is pasting "Dakotaland" data and "City of Brandon" data on the "Dakotaland" worksheet instead of putting the "City of Brandon" data on the "City of Brandon" worksheet. Could you help in resolving this issue?
Thank you in advance!
Louis Kavetski Below is the code I'm using. "CHAD FORD" is the data going to the "ANTHONYIDANK" worksheet instead of the "CHADFORD" worksheet.
Private Sub CommandButton21_Click()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 6 To LastRow
If Cells(i, 1) = "ANTHONY IDANK" Then
Range(Cells(i, 1), Cells(i, 9)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\kavetskil\Desktop\DAILY ACTIVITIES\WHAT.xlsx"
Dim p As Integer, q As Integer
p = Worksheets.Count
For q = 1 To p
If ActiveWorkbook.Worksheets(q).Name = "ANTHONY IDANK" Then
Worksheets("ANTHONYIDANK").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
If Cells(i, 1) = "CHAD FORD" Then
Range(Cells(i, 1), Cells(i, 9)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\kavetskil\Desktop\DAILY ACTIVITIES\WHAT.xlsx"
Dim c As Integer, d As Integer
c = Worksheets.Count
For d = 1 To c
If ActiveWorkbook.Worksheets(d).Name = "CHAD FORD" Then
Worksheets("CHADFORD").Select
End If
Next d
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
Louis Kavetski Worksheets("ANTHONYIDANK").Select
Worksheets("CHADFORD").Select
Is your worksheet name ANTHONY IDANK or ANTHONYIDANK?
Same question: CHADFORD or CHAD FORD?
Once you correct the names, the code should work.
Dinesh Kumar Takyar WOW! I kept reading right over that. Such a simple fix. Thank you!
Dinesh Kumar Takyar How would I open the workbook and keep it opened until all data (rows) have been pasted over to it?
I ask because sometimes I have 70+ rows of data that need to copy over to the workbook that i am opening. The way the code is now it Copies data>opens other workbook>pastes data>saves>closes for each row.
I think it would be much wuicker to have it open one time, pastes all the data and then save and close.
Thank you in advance!
Hi Dr. Takyar! I am struggling with this VBA tutorial. I am not able to get any data to copy to a worksheet from my master workbook. This video seems pretty comprehensive, however, I am still really confused!. I would like to take data from one master worksheet and break it up into separate documents.
We do payroll using excel and divide up our payroll by markets. Boston, California, South Carolina, etc. There are 19 columns with different, but critical information. I would like the identifier to be each markets (all markets can be found in one column), and the name of the sheet to mimic the name of the identifier (Boston, South Carolina). With 25 markets and roughly 1000 line items, lots of errors are made transferring the data. Is there anyway you can help. I would upload my file, but I cannot share payroll on youtube :).
Thanks so much!
financeguy1225 Try these videos for a solution:
ruclips.net/video/lyNwuXrUAoM/видео.html
ruclips.net/video/TZptQpmW2Xs/видео.html
Is is just me or do the codes differ slightly between macs and pc?
Sir, Thanks for your smooth training style. I have some problem with the code. I copied the code from your link and paste it to my module but it does not work. When I run the code it stops at first row where we are setting the value of "lastrow". Please help me.
Copy paste does not work. If you check the pasted code carefully you'll detect the errors. Note the apostrophes and quotes.
When we run Macro. Copy XML data(Live Data) in one table with time intervels of 3 mints thourgly copy at selected cell with Current Time in the Down of that table but old data was be not arriased ...
Anybody Help Me.
Thankq in Advance..!
Hi Sir, would you help me in providing the codes if I would like to copy ALL rows (no conditions) and paste it to another workbook? Is it possible if we don't use a loop because it take a lot of time especially when my data contains a thousands of rows. Conditions transfers the data one row at a time.
Thank you in advance. You're page is very inspiring.
Any code for delete rows from 50 th row to 100 th row,. In this code 100 the row is stable but the 50 th row is a manual input it may varies and I want to declare that value from input dilog box.. Can u please help me out, thanks in advance
Use a looping process.
hello sir, your video is helpuful for use, but in this video as your second line that is to count last row is not working in my workbook, please help me to solve it, i just want to copy multiple data from one workbook to another
Me too - is not working - 2nd line
Hello - do u know why I and other users are getting error in second line of code?
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
Are you using the data in Range D?
hello Dinesh sir...
I understood what you taught us and I have doubt, instead of "dakudaland" in that Excel, there are so many names I have to separate from master to another. what can I do?
You can use 'case' or 'elseif' or an 'inputbox'. Search www.exceltrainingvideos.com
Hi Sir,
I was referring for a vba code for which it may help me as follows:
1) Master sheet (which will be updated manually)
2) Book 1 (using VBA codes the certain cell data will populate by clicking a button from Master sheet)
3) Book 2 (")
(question: how to copy paste from one master workbook to another workbooks using vba macro code)
can u pls help!
Sir thank you kindly for the information you shared. It helped to solve my problem.. have a good day!!
i want to copy and paste in the 2nd sheet but i want to paste the value in A1 and B1 7 times in the next sheet could you please help me with the code sir
Let's assume our data is in Sheet1 and we wish to copy it to Sheet2:
Sub copyOncePasteMultiple()
Dim i As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
For i = 1 To 7
ws1.Range(Cells(2, 1), Cells(2, 2)).Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub
Dear sir, the above video is very helpful. I'd like your help in a excel file which has data (without functions) in different cells. It's not a group of data in a row or column. The excel sheet has full of such data (text & number), but no functions. The data i want to collect is spread out. Eg: one data is in d4, other is in f11 and so on. Now i need to copy these (around 9 data cells) particular cell data to a new workbook. Can you please help me out... Thanks a lot... Below i want to point out again.
* I've around 7 to 20 different workbooks with same layout.
* I want to collect the specific individual data cells to a new workbook from all these workbooks.
situ tad This link might help: www.exceltrainingvideos.com/automate-copy-paste-data-in-non-adjacent-cells-in-sheet1-to-sheet2-with-vba/
Hello sir,
I have a worksheet in which I want to look up, thank you that it can only be done via VBA, in cell CF4 I have the following formula: = RESEARCH ($ EA4; $ A $ 3: $ CV $ 21; 3; FALSE).
EA4 goes up to AE22
The "3" must be able to change because the data is in different columns.
In Cell EC4 I have the formula: = If ($ EA $ 2 = 1; $ EF $ 4; 0)?
The number "1" goes up to "20", what do I want to do first, the two formula in a cell BV in EC4.
Then I want to search the row below etc. then in the column next to it (there are three in each case).
There are 20 rows.
Can you help me?
What is RESEARCH?
I mean Vlookup.
Hi Dinesh Sir.. I have tried this coding but only 1 row of data is geting transfered to another workbook. Can I have your mail id so that I can send the file to you
how to take data from different workbook using vlookup in vba plz help me
+Rahul Viswakarma This video might help: ruclips.net/video/nm5nbb524tU/видео.html
Hello Sir , how to include Header in the above code, I need to include Header too
Have a look at this link:
Copy Paste Data from Multiple Rows from one Workbook to Another using Excel VBA
In the looping process your start value should be 1 or the row(index) where your headers are located.
First time Posting so not sure how to go about this.I have a spreadsheet that I am trying to apply this code to with some slight modifications for my use. (see code below)trouble is that not all of the rows with my "X" search criteria are being brought over to my ("MACRODESIGN") Worksheet.can anyone give me some insight as to why some items are being copied and others are being passed over?I have a COMBINEDSHEET sheet with all of the raw data (approximately 250 rows) and then a MACRODESIGN sheet that will contain all of the items that I identify with an "X" in column "A".The intent is to update/clear out and then replace, the data on the MACRODESIGN sheet whenever I click on the button that initiates the macro.Currently it does a great job of clearing out the MACRODESIGN sheet, but then the issue comes when data is being copied over from the COMBINED sheet to the MACRODESIGN sheet.I can't determine the common issue with why some items are "captured" and some items are missed.I am fairly new to VBA (been playing with it for two days now) so any suggestions would be welcomed.Sub MACRO_DESIGN()'SELECT DESIGN SHEET
Worksheets("MACRODESIGN").Activate
'CHECK CELL A2 TO MAKE SURE PAGE IS CLEARED OUT
If Cells(2, 1) = "X" Then'SELECT MACRODESIGN SHEET
Sheets("MACRODESIGN").Select
'DELETE EVERYTHING IN THE EXISTING MACRODESIGN SHEET EXCEPT FOR THE HEADER DETAILS
Range("A2:A300").EntireRow.DELETE
End If
If Cells(2, 1) = "" Then
Worksheets("COMBINEDSHEET").Activate
'ASSIGN INTEGER
Dim i As Integer
'SELECT COMBINEDSHEET
Worksheets("COMBINEDSHEET").Activate
'IDENTIFY THE RANGE ON THE ACTIVE WORKSHEET FOR THE CRITERIA TO LOOK THROUGH
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'IDENTIFY THE LAST ROW FOR COLUMNS TO SEARCH THROUGH
For i = 2 To LastRow
'SEARCHES COLUMN A FOR DESIGN DESIGNATION
If Cells(i, 1) = "X" Then
'IF COLUMN A CONTAINS DESIGN SELECT ROW WITH DESIGN
Range(Cells(i, 1), Cells(i, 676)).Select
'COPY SELECTION
Selection.Copy
'IDENTIFY THE WORKSHEET THAT CONTAINS NAME MACRODESIGNWorksheets("MACRODESIGN").Select'FIND THE FIRST EMPTY ROW IN SHEET2
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'PASTE THE DATA HERE
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
'END IF ARGUEMENT
End If
'GO TO COMBINEDSHEET AGAIN AND ACTIVATE IT
Worksheets("COMBINEDSHEET").Activate
'LOOP THROUGH THE OTHER ROWS WITH DATA
i = i + 1
Next iEnd If
End Sub
+Adam Neeley In a for next loop you need not increment the integer i as you've done: i=i+1.
+Dinesh Kumar Takyar -Thank you very much. I'm excited that it was something that simple. This simple solution made the spreadsheet work flawlessly. Very Excited.
Hello Sir,
Its very good videos and the sytle of teaching is very much understanding. I would like to have the contact details, or where I can put my questions,
Thanks
Naven
Naveen N You can place your queries right here.
It working great, but when I update new data then I click the commandbutton again it paste row from previous again make the other workbook has duplicate file. How to copy the new data only?
Piseth Yoy Before you do a copy paste you can clear the data from the worksheet where you are pasting!
Hello! Did you ever tried the Smart Cash Secret (just google it)? Ive hear a number of extraordinary things about it and my buddy made lots of wealth quick.
plz create vba code blank row delete multiple sheets in difrent -2 data
Query or suggestion not clear.
In case great than 10,000 rows . How we can do ....?
What have you tried and what are the problems you faced?
Hello Sir
Tried working on it is not working seems that I have missed some code can you kindly share the full vba Code
Get the complete VBA code here: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
You may also search www.exceltrainingvideos.com for more info.
Thanks a Lot a Sir for your prompt response.
The records are being duplicated say record 1 is also copying again if the details are being added on the workbook1 on running the macro againRequest your kind assitance
Sir DO you have answer to my question??????
Hello Dinesh, I get following error:-
Run-Time error '1004'
Application-defined or object-defined error
Please advice.
Many thanks
Kind regards
Mehool
Rashna Shah Check your code line by line.
@@Exceltrainingvideos 2nd line doesn't work
Firstly thank you very much for your education but I have an different question professor. Could you please share your e-mail? I really need your help professor.
takyardinesh@gmail.com
No guarantee of help but I will try.