HomeОбразованиеRelated VideosMore From: Dinesh Kumar Takyar

Transfer Data from Multiple Workbooks into Master Workbook Automatically

1164 ratings | 476085 views
We can transfer data from multiple workbooks into a master workbook automatically using Excel VBA. Sherin has created a template file with specific headers. Her suppliers have also been instructed to have the same headers with the relevant data below them. Now when the suppliers send her the workbooks by email, Sherin transfers them to a common folder where she has her template or master file. She wishes to now automate the process of transferring the relevant data from the suppliers files to her master file. The process involves the following VBA code: (1) Find out how many suppliers files are there in the folder (2) Open the files one by one (3) Copy the relevant data (4) Close the suppliers file (5) Paste the data into the template or master file (6) Loop through all the files using the LEN function with the DO WHILE loop Finally we can call our subroutine or macro every time we open the workbook to automatically transfer the data from the suppliers files to our master file. Code and further details: http://www.familycomputerclub.com/transfer-data-from-multiple-workbooks-into-master-workbook-automatically-using-vba.html Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Html code for embedding videos on your blog
Text Comments (494)
ADHAR SHARMA (19 hours ago)
Hi, Sir if we want to do it from one work book which having many worksheet. Please make a video of that also.
Dinesh Kumar Takyar (7 hours ago)
This link will help: http://www.exceltrainingvideos.com/tag/get-data-from-multiple-worksheets-in-multiple-workbooks-into-master-workbook-with-vba/ Or search http://www.exceltrainingvideos.com
Imran Immu (18 days ago)
I m getting Say "Sorry ,we couldn't find sheet(1).xls Is it possible it was moved,renamed or deleted?, Kindly help me with Solution. Thank you
Dinesh Kumar Takyar (17 days ago)
Check the name of your sheet1. In the VB Editor you can click on Project Explorer to view details.
uzair momin (21 days ago)
This is for Copy only Single line How to Copy all data
Dinesh Kumar Takyar (21 days ago)
This link will help: https://www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/ Or search https://www.exceltrainingvideos.com
Sheikh Nasir (22 days ago)
Sir, I want to create bank reconciliation statement in excel which is online connected our bank & reconcile automatically. and my reconciliation report create easily on excel. Is it possible?
Dinesh Kumar Takyar (22 days ago)
This link will help: https://www.exceltrainingvideos.com/bank-reconciliation-using-excel-vba/
Phanith Reach (1 month ago)
Thanks so much for your kindness. In case I add another column on master file, supplier name. Supplier name is from file name or workbook name. Thanks in advance!
Sankaresh Kannan (1 month ago)
Dear Sir, How to automatically update only specific updated rows from 2 workbook to Master Workbook?
Dinesh Kumar Takyar (1 month ago)
This link will guide: http://www.exceltrainingvideos.com/tag/track-changes-with-vba/
Sankaresh Kannan (1 month ago)
How to automatically update only specific updated rows from 2 workbook to Master Workbook?
Victor Levy (1 month ago)
Hello, I have an issue, where I have a master workbook that has 2 sheets. what would be the correct formula to get the data from one sheet onto the correct master workbook sheet. What am saying is how do I get sheet1 data to be copied only to sheet1 master workbook, and sheet2's to be copied to only sheet2 master workbook.
Elmondu (2 months ago)
Dear Sir, I really very thankful of your videos and it's very helpful and well explained by you very clearly. I just got your example that suited to my problem about transferring data from multiple workbooks. On this, I just want to know how to paste the date in Row 3 which is C3 and it took a lot of adjustments to move that pasting but it didn't work at all. I'm just a beginner. Thanks a lot sir.
Elmondu (2 months ago)
how to paste the data...correction
Durga Gireesh (2 months ago)
Hi brother your code I have tried but I am getting some debug error because I have large file . When I take small size files it is working fine please advise
Dinesh Kumar Takyar (2 months ago)
This link will help: https://www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
Agent Orange (2 months ago)
great!
tahir bari (4 months ago)
Dear Respected Sir, really i am great learning from your teaching ...... i need more learning how to make summary or report reconciliation our warehouse material at store....waiting your good response
Atlas Holidays & Events (4 months ago)
very Nice
Audioactivo Productions (4 months ago)
thank you for the video, how could i add some code to provide an extra column with the file name of each data row
Audioactivo Productions (4 months ago)
Dinesh Kumar Takyar thank you, what part of the code would i add this too? thank in advance
Dinesh Kumar Takyar (4 months ago)
ActiveSheet.Cells(erow, 5) = MyFile
globlal zone (4 months ago)
thx mutch 4 that
manoj kumar (6 months ago)
Hi Sir, Thank you very much. I am a beginner with Marco and this video was really helpful to get in the data from multiple files to master file at work.
Houssam Zakaria (6 months ago)
Thank you a lot, it worked. How can I refresh the data collected without repeteded entries? Thnks
Alex Rosén (7 months ago)
I get an error message from "erow = Grunddata.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row". Can you please help me?
Dinesh Kumar Takyar (7 months ago)
https://www.exceltrainingvideos.com/refer-to-sheets-by-name-vba/
padmini71 (8 months ago)
I have more than two sheets in each workbook. how to combine each sheet in my zmaster
Dinesh Kumar Takyar (8 months ago)
https://www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
padmini71 (8 months ago)
sir, I am getting 1004 error. how to resolve
Shaho Habeeb (8 months ago)
Hello Dear thanks a lot for your great video , please tell me why when it reaches the exit sub its go back to the fist line doesnt continue to open the exist files , I hope you reply me .
Dinesh Kumar Takyar (8 months ago)
exit sub ---> macro stops running.
arun dubey (8 months ago)
hello sir can you show me this code in Powershell ? If possible ?
Sudeep J (8 months ago)
What should be the code used if the data needs to be copied are different in each files
Dinesh Kumar Takyar (8 months ago)
Use the 'UsedRange' property.
Nur uddin (9 months ago)
Thanks. the code is working...with this code pls add more option such as paste with fill color, font color and also formats.
Teofilo Tercio (9 months ago)
im getting a Compile error: Expected:As for Workbooks:Open (MyFile)
Dinesh Kumar Takyar (9 months ago)
Buy a book in basics of VBA: Excel VBA Programming for Dummies
eraloushie (11 months ago)
Hi Sir.. May I know if it will work if my workbooks are from another computer in a network?
Dinesh Kumar Takyar (11 months ago)
Try it out!
Mohammed Munir (11 months ago)
Hi Dinesh, Your videos are great..my question is my data are in 2nd sheet and macro enabled sheet how do i amend code please help me.
Dinesh Kumar Takyar (11 months ago)
This link should help: https://www.exceltrainingvideos.com/methods-to-transfer-data-from-excel-worksheet-with-vba/ Key is the path.
mahendra singh rao (11 months ago)
Too good sir
V2S (1 year ago)
This is my first Macro, I use office/Excel 2013 and I tried exactly as described whether it file contents. File names folders etc also I am not getting any errors what so ever but no data is getting copied. Any idea why this is still not working?
Dinesh Kumar Takyar (1 year ago)
Share your code.
Vaidehi1 Chennubhotla (1 year ago)
Hi Dinesh Sir, Can we use a file dialog to choose the excel files from a folder and paste all the data from all excel files into on single excel sheet of a workbook one below the other? Regards Vaidehi
Dinesh Kumar Takyar (1 year ago)
Good question. Let me work on this.
feeldennis (1 year ago)
Good stuff, but i got stuck, was trying to copy column B1:B8 ( multiple workbooks) to rows ( master workbook) as in the example above. Any ideas Please
SUBRAMANYAM D (1 year ago)
hi sir i have multiple range of cells how do i add all of it please help me sir my cell ranges are #("B7", "B8", "A12", "B12", "C12", "D12", "F12", "H12", "H7", "H8")# please help me sir
Dinesh Kumar Takyar (1 year ago)
https://youtu.be/W_SjBQBdlEE
vijay kidecha (1 year ago)
I have a folder that contains number of folders(which is dynamic,I don't know the count). Each subfolders has some files which is variable. Now I want to combine file from all the subfolders to a single file. How do I do that
Dinesh Kumar Takyar (1 year ago)
https://youtu.be/a5ZAIurKg3I
Shrikant Pancbhai (1 year ago)
Sir , I am using the code that you have shown in the exact way(using excel 2010). It is showing the file name which is Bangalore and it is finding the file from the given path but still I am getting this error as below .. Run-time error '1004'': 'Bangalore.xlsx' could not be found. Check the spelling of the file name and verify that the file location is correct. If you are trying to open the file from your list of most recently used files , make sure that the file has not been renamed, moved or deleted. Would greatly appreciate if you could help me as to what could be the reason I am getting this error. I am using company laptop to do this but I even tried to log in using the admin account but I am still not able to sort this error.
Shrikant Pancbhai (1 year ago)
No sir the file extension is Bangalore.xlsx only the master file where I have written macros is .xlsm
Dinesh Kumar Takyar (1 year ago)
Is your filename 'Bangalore.xlsm' by any chance?
Erick Gomez (1 year ago)
Good evening Sir. First of all, your video is amazing! Thank you very much for helping me understand this a little more. I just have a question. What happens if the data I intend to copy is organized in Columns and not in Rows like in your example? I am sure the first part should be the same, but when I run it, the data keeps on pasting on the same space... Could you help me with this? Thank you in advance.
Dinesh Kumar Takyar (1 year ago)
Use a looping process. Let's say the first set of data goes to the first column. Now we need to increment the column value by 1. That is, x=x+1, This link will guide: http://www.exceltrainingvideos.com/tag/find-last-column-automatically/
Rohit Parashar (1 year ago)
Hi Sir, I have multiple workbooks and I have data in multiple sheets. I want to combine data in all the sheets 1 and then I want to combine data in sheet 2 on a separate sheet. Can you help me with the code for this??
nadie de guzman (1 year ago)
it is very helpful to a beginner like me. thanks... but so far i still manage to learn it step by step.
Bad Wolf (1 year ago)
Hi, I have used this code and it works perfectly and I am so thankful! Is there any way I can update the syntax by making excel copy and paste my values in exactly the same format? It gets confused with decimal numbers derived from a formula in a different excel workbook. I have tried “pasteSpecial” but it’s not working. I have looked on other websites but it seems I need to change the whole code just to make MACROS doing it and I am a bit lost. Thank you for your videos!!
Sweety Singh (1 year ago)
+Dinesh Kumar Takyar I am unable to run the code for more than 3files.. why is that so.please suggest
Dinesh Kumar Takyar (1 year ago)
This link will help: https://www.exceltrainingvideos.com/tag/copy-table-data-to-new-worksheet-with-vba/ Or search https://www.exceltrainingvideos.com
i have a question.Please provide your gmail address so that i can send you excel file.
Dinesh Kumar Takyar (1 year ago)
Ravi Vaishnav (1 year ago)
PLEASE FIX YOUR MIC........ YOUR VOICE IS SO IRRITATING
Mei Zhang (1 year ago)
Hi Sir, If the files names are different, may I move all the data from different sheet to one master sheet with excel 2013? Thanks
Dinesh Kumar Takyar (1 year ago)
Once you are in a unique file (workbook), you can loop through each of its worksheets, grab the data step-by-step and copy them to the master worksheet. I've a few videos on this topic. You can search at https://www.exceltrainingvideos.com
Kalubala Gilbert (1 year ago)
Good evening Mr. Kumar I have a problem to avoid in excel VBA to repeet one ID number ot be attributed to both person.
Dinesh Kumar Takyar (1 year ago)
Use countif.
Nikhil Satheesan (1 year ago)
How do I use pastespecial command in this code.. I need to copy data including formatting (colour width hyperlink) etc.. Pls help
Dinesh Kumar Takyar (1 year ago)
Search my channel or website https://www.exceltrainingvideos.com
Surya V (1 year ago)
i write the below coding. my purpose is to copy some specific set of cells (ay8 to eh8) from around 720 sheets at each month end..for doing so i have to open all sheets and hence to avoid it I was looking for a program..all the thing went smooth for the below code...only thing i could not able to copy the contents in order..i mean 1st hr file 2 nd hr file 3rd hr file and so on 720th hr file...when copying to the master sheet all the contents from different 720 sheets copying to master sheet except its not copying in order.......just check the below code and can you suggest anything to avoid it.. how can we open workbooks based on created time and copies to the master sheet..can you please modify the below VBA code I have pasted and send me....thats a great help from you... File Naming is like below...based on time stamp the file name changes and these below files generates automatically from all of which 720 files i have to copy data to master sheet. I am partially succssful except sorting..however i know i can do sorting in excel after copying the data...but my intention is to put the sorting option in macro coding itself. 2017_7_1_0_1(Year_Month_Date_Hour_1) 2017_7_1_1_1 2017_7_1_2_1 2017_7_1_3_1 2017_7_1_4_1 . . . . . 2017_7_31_23_1 (Year_Month_Date_Hour_1) The code I had is below Sub simpleXlsMerger() Dim bookList As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files here Set dirObj = mergeObj.Getfolder("D:\CSV Files Consolidation\CSV Raw Files") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) 'Put the Range of cells you would like to copy Range("B8:EH8").Copy erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as above Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False bookList.Close Next End Sub
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com
silver styles (1 year ago)
Hello sir, just today i tried you coding which is great however when i ran the macro it copied only first row from each workbooks into the zmaster workbook?
Dinesh Kumar Takyar (1 year ago)
Check your code again! This link will also help: https://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Arun Mano (1 year ago)
Hi, please tell me how to do, if my Workbook contains much more data in it? For example I want to copy starting with A2 to AK462 (as the range) but this range is different from one file to another. Is it possible to do this with this code in good manor? Because it run when i give range like A77:S92 (range), But when am run it, it Popuping permission for each worksheet if i click ok it will copy and pasting it to destination workbook! How to avoid it, ?? YOu can set the range which i mention above and retify me - Thank you in advance!
Sharwan Kumar (1 year ago)
thank you sir, i really need this code for consolidate my official worksheets.... thanks alot...
Nitin Sharma (1 year ago)
hello Sir, its my first VBA excel video, You solved my main problem. But my question is that can i get the file name also from which i copying data in a extra column.
Ubaid Ullah (1 year ago)
Sir, My issue is a bit different. I have followed the same path. But when I pressed run, system is saying that Master file has either been removed, renamed or .... Though the file is there in the folder. How to fix this issue.
Dinesh Kumar Takyar (1 year ago)
These links will help: http://www.familycomputerclub.com/transfer-data-from-multiple-workbooks-into-master-workbook-automatically-using-vba.html https://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
Vaidehi1 Chennubhotla (1 year ago)
Yes! I got it. This is my first VBA macro ever! I got every possible error - run time error 1004, run time error 424, run time 9. Guys, this is the code i followed and I got the result perfectly fine. Sub LoopThroughDirectory() Dim MyFile As String Dim erow Dim Filepath As String Filepath = ("C:\suppliers-master\") MyFile = Dir(Filepath) Do While Len(MyFile) > 0 If MyFile = "zmaster.xlsm" Then Exit Sub End If Workbooks.Open (Filepath & MyFile) Range("A2:D2").Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4)) MyFile = Dir Loop End Sub Sir, my next question is - I want to use a different format Excel file called .csv It is from Xsensor software used in the automotive sector. I determine the pressure distribution on a car seat using pressure mats. I extract the pressure values into excel as .csv file. Which file extension should i use for the zmaster file? zmaster has to be a macro enabled excel file, right? But, I need the zmaster also as a .csv so that I can calculate the grand average of the pressure distribution and reuse the .csv file in my Xsensor software for further analysis. Sir, I am a Mechanical Engineer and I dont have much background in coding. Could you please help me ? Sir, I want to create a marco which find the average of values in 10-20 excel workbooks and then put the final data in a zmaster file. All workbooks have equal number of pressure readings. Kindly help. Thanks & regards, Vaidehi
Vaidehi1 Chennubhotla (1 year ago)
Sir, I copied your code and did exactly as you said in the video. But I am getting "Run time error '1004'. Sorry, we couldn't find supplier-a.xlsx. Is it possible it was moved, renamed or deleted?' I have the supplier-a.xlsx in the same location as you have shown in the above video. And under the same folder name. Also, I have saved the supplier-a, supplier-b, supplier-c as .xlsx files and the zmaster as .xlsm file. Why am I getting this error? When I ask it to debug, it is pointing to Workbooks.Open (MyFile) This is the exact same code as yours Sub LoopThroughDirectory() Dim MyFile As String Dim erow MyFile = Dir("C:\suppliers-master\") Do While Len(MyFile) > 0 If MyFile = "zmaster.xlsm" Then Exit Sub End If Workbooks.Open (MyFile) Range("A2:D2").Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4)) MyFile = Dir Loop End Sub Please help. Thanks & regards Vaidehi
Brian Jensvold (1 year ago)
The files im importing from have more than one row, also it keeps opening the same source file instead of moving on to the next file. Sub LoopThroughDirectory() Dim MyFile As String MyFile = Dir("C:\Users\Jensvold\Desktop\Brian\Lowes\") Do While Len(MyFile) > 0 If MyFile = "zzz DoNotDelete.xlsx" Then Exit Sub End If Workbooks.Open (MyFile) Range("A5:K5").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveWorkbook.Close Windows("LowesFile.xlsx").Activate Range("A1").Select Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlUp).Select ActiveCell.Offset(1).Select ActiveSheet.Paste Loop End Sub
Dinesh Kumar Takyar (1 year ago)
Check your code again.
Jennifer Berry (1 year ago)
Hi Sir, I complete a monthly excel worksheet and would like select columns of data to be copied automatically to a separate yearly workbook using VBA. The data format is always the same. This will allow me to view current and prior months side by side periodically during the year.
Mallappa K (1 year ago)
Dear sir, i am mallappa a'm doing( e-commerce executive)sir i suffering in excel, ex some formulas and macro also so kindly help me
Prakash Praaku (1 year ago)
Hi Sir, I need your help on how to move excel data into Access database on daily basis, I will have an excel file with 15 columns and some 3000 rows of data everyday, how can I send this excel data into one access database, Thank you in advance, Prakash, Bangalore
Dinesh Kumar Takyar (1 year ago)
There's an option in Access under 'External Data' to import Excel data into Access. You may be able to record a macro.
TheYazzing (1 year ago)
Hi, I think you have the best video here for this topic. it is simple and easy to understand. Unfortunately, your code doesn't work for my files....
Dinesh Kumar Takyar (1 year ago)
Check your code again - carefully!
neelakanth veluru (1 year ago)
Hi Dinesh ji, Please have a look at my code. The files are not being opened although there are files available in the path defined. Sub LoopThroughDirectory() Dim MyFile As String Dim Filepath As String Dim erow Filepath = "C:\Microsoft_Macro\" MyFile = Dir(Filepath) Do While Len(MyFile) > 0 If MyFile = "Master.xlsm" Then Exit Sub End If Workbooks.Open (Filepath & MyFile) ActiveSheet.Range("A2", ActiveSheet.Range("A2").End(xlDown)).Select Selection.Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 1)) MyFile = Dir Loop End Sub
Dinesh Kumar Takyar (1 year ago)
Without some thought and some hard-work you'll not be able to solve the problem. You are trying to paste data from multiple cells into one SINGLE cell.
neelakanth veluru (1 year ago)
Dinesh Kumar Takyar sorry ji I could not find the issue. i want to paste in only 1 column.
Dinesh Kumar Takyar (1 year ago)
Check your code. Tip: the paste part!
Kyaw Thu Htet (1 year ago)
I have created an excel template with several spaces into which I want to import data from MS Acess database. The amount of rows used would be of varying length each time, as the database is updated. I want to set it up so that when the data is imported, it will not overwrite the existing template with the extra data, but instead will insert new rows, and "push" the lower parts of the template down further on the page.
Kyaw Thu Htet (1 year ago)
Hi Dinesh, I have looked through the website and could not find any thing that could solve my problem. I would really appreciate if you could give me some guidance on this. I am willing to buy the video tutorial if there is a video that could teach me how to write the code to solve the above mentioned problem. I would really appreciate your help and your time. Thank you. John
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com
Dhanraj Ganatra (1 year ago)
+Dinesh Kumar Takyar Hello sir great video, i used the vba code from the website, but only the first row gets copied to the master sheet, please slove this issue. Thanks in advance.
Dhanraj Ganatra (1 year ago)
Dinesh Kumar Takyar Thank you very much sir I will check again.
Dinesh Kumar Takyar (1 year ago)
Copy/paste doesn't always work. Check your code line by line specially for the quotes!
Naveen Jain (1 year ago)
Hello Sir, I have been struggling to consolidate various workbooks in Excel to one workbook. I have 20 different workbooks with identical header saved into a specific folder. Each workbook sheet 1 has about 30,000 rows which I want to consolidate into a one file. I have used the following VBA code but it stops copying data after row 90,000. Please provide help so that all rows are copied. ___________________________________________________________________________ Sub simpleXlsMerger() Dim bookList As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files here Set dirObj = mergeObj.Getfolder("\\mypath") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here 'for example "B3:IV" to merge all files start from columns B and rows 3 'If you're files using more than IV column, change it to the latest column 'Also change "A" column on "A65536" to the same column as start point Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as above Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False bookList.Close Next End Sub
Dinesh Kumar Takyar (1 year ago)
Sub simpleXlsMerger() Dim erow As Long Dim bookList As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files here Set dirObj = mergeObj.Getfolder("C:\myfolder") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) Dim lastRow As Long lastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Dim lastColumn As Long lastColumn = Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Range(Cells(2, 1), Cells(lastRow, lastColumn)).Copy ThisWorkbook.Worksheets(1).Activate lastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row erow = lastRow + 1 Sheet1.Cells(erow, 1).PasteSpecial Application.CutCopyMode = False bookList.Close Next End Sub
Paresh Jakharia (1 year ago)
Sir, I did write the codes as per your instructions. But nothing is copied to the master file. Can I send you the codes or the files I am working on through email. Pls advice
zee gemini (1 year ago)
plz help me how i can automaticaly data from 1 xlxs sheet to 2nd page or sheet
Dinesh Kumar Takyar (1 year ago)
Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU Or visit https://www.exceltrainingvideos.com to learn more for free.
Jayant Patil (1 year ago)
Hi Sir,I just want to record same method in excel, Is it possible?? Please share....
Jayant Patil (1 year ago)
I know how to record and edit the macros plz advice with same problem
Thushara Indralal (1 year ago)
This is a detailed explanation, thank you so much Sir
Mohamed Mihlar (1 year ago)
how to create Like a pos system stock manag. Ex. Today Sale 10 Qty Automatic Less 10 qty Stock Manage system.
raju lakhani (1 year ago)
Dear Sir, Saw ur many videos and they such helping, i have 3 query in this video as below. 1) Some times i have 300 files in folder and some times 200 do i need to change anything in code? 2) What if i want to copy cell no B6 and E23 to F23 ?? i done every thing but never success 3) and there is one problem cells data always select 1st row only never went down Hope u will help me Thanks so much
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com or Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Shivani Patankar (1 year ago)
sir plz tell me how to copy data into excle by using macros from another software. ..
Mohideen king (1 year ago)
Dear Sir, I have 5 files with same header in different location . End of the day i want to combine the data from this 5 file to master file. but i got some code with my friend below mention, sub simpleXlsMerger() Dim bookList As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files here Set dirObj = mergeObj.Getfolder("C:\Temp\ExcelFilesMerge") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here 'for example "B3:IV" to merge all files start from columns B and rows 3 'If you're files using more than IV column, change it to the latest column 'Also change "A" column on "A65536" to the same column as start point Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as above Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False bookList.Close Next End Sub but this code extract in one folder,, how can i write code for different folder location and specific file name. and how to mention password array code for open file ( becaz all excel file password protect) kindly help me out please.
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com
Ritu Sirohi (1 year ago)
Hello Dinesh Sir, I want to copy data from 3-4 different excel workbook and paste it into different sheet in another one common workbook. But everyday data will change and number of rows will increase, Is it possible with the help of VB/macro. If yes, then please tell me asap. Thanks in advance.
Ritu Sirohi (1 year ago)
Not like this Sir, suppose take a example you have 3 dump file and 1 common file then you have to copy data from 3 different workbook and paste into different sheets (sheet 1, sheet 2, and sheet 3) in the common workbook. Tell me how? And one more thing every day number of rows in dump workbook will be increase and sometimes rows will be decrease. If possible can you please help in this?
Dinesh Kumar Takyar (1 year ago)
This link should help find a solution: http://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ Or search http://www.exceltrainingvideos.com
Mel Bayliss (1 year ago)
Firstly a massive thank you for saving me hours of work, 300k+ records merged in minutes. I have an issue you may be able to help me with, when trying to merge files around 4mb or larger creates an error in the paste line?
Manmohan Gupta (1 year ago)
Thank you so much sir my search got destination ...made all my work automated
Karen Kaur (1 year ago)
Dear Sir, Thank you for providing such a useful tool to make work easy. I am trying to import/copy from multiple cells from multiple worksheets into master workbook but unable to figure out my errors and how to add cells other sheets from source workbook please guide. Regards Karen Sub LoopThroughDirectoryAAS1() Dim MyFile As String Dim erow Dim Filepath As String Filepath = "E:\1 -2016\" MyFile = Dir(Filepath) Do While Len(MyFile) > 0 If MyFile = "zAnnualActivityMaster2016.xlsm" Then Exit Sub End If Workbooks.Open (Filepath & MyFile) Sheets("Load Startup1").Select Range("B20,B1,B3:B4,B14:B16,B6,B10").Copy ActiveWorkbook.Close erow = Sheet8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("AAS1").Range(Cells(erow,1), Cells(erow,9) MyFile = Dir Loop End Sub
Dinesh Kumar Takyar (1 year ago)
This link will also help: http://www.exceltrainingvideos.com/tag/copy-paste-non-contiguous-cells-excel/
Dinesh Kumar Takyar (1 year ago)
http://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ Or search http://www.exceltrainingvideos.com
Eyqa yusof (1 year ago)
hi sir, ive copied your codes and there is still error on "workbooks.open(MyFile)" thanks
Eyqa yusof (1 year ago)
Yes got it! Thank you sir
Dinesh Kumar Takyar (1 year ago)
After copying you need to check each line carefully and correct any errors due to copy paste.
omar al-Qaysie (1 year ago)
Thank you very much, it's a wonderful lecture. I have a question I hope to answer. What if we have more than one record of information on each Sheet within a workbook , and want to merge all data from all workbook in one sheet of master workbook.
Dinesh Kumar Takyar (1 year ago)
http://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ Or do a search at http://www.exceltrainingvideos.com
Rome Hong (1 year ago)
i put the VBA as follow but not working ub LoopThroughDirectory() Dim MyFile As String Dim erow MyFile = Dir("S:\Business Process Management\Hand Over to Jerome Hong\AFCE") Do While Len(MyFile) > 0 If MyFile = "AFCE - Group.xlsm" Then Exit Sub End If Workbooks.Open (MyFile) Worksheets("Appendix 3").Range("A12:AU200").Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("appendix 3").Range(Cells(erow, 1), Cells(erow, 200)) MyFile = Dir Loop End Sub
Dinesh Kumar Takyar (1 year ago)
This link will help: http://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Rome Hong (1 year ago)
Hi I got a multiple worksheets in one macro excel file but need to copy to combine all the data in in a specific worksheet to master file. the worksheet name for all the macro excel file is the same. i have 500 macro file to pull to this master worksheet. could you advise can this be done? all are having same heading n column
Akbar Mahfuz alam (1 year ago)
Hi Dinesh Sir, I have 10 userform as userform1 ,2 3 .... 10 and created quiz on each userform with Frame and radio button and on the last userform- Submit Button  I need the correct answer score in a  excel sheets. Thanks in advance. Rgds, Akbar Alam
Roger Tong (1 year ago)
Hi Dinesh Kumar Takyar,I have question what if supplier-a, supplier-b & supplier-c contain the following in their "This Workbook". Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" End Sub Error:-Run-time error '1004':Paste method of Worksheet class failed Is there any way to overcome this problem?
nouh (1 year ago)
Thank you sir for your videos. It is really indispensable to watch. I have a question about the easiest way to make a single Excel workbook which will summarize 20 workbooks. Each workbook includes 15 sheets. The data present in all workbooks and sheets is arranged by same way. It reveals sales of food items for various time intervals. Say each sheet represent sales for 3 months from each year, and each workbook represent certain items sold. What is the easiest way to make a master workbook that contain the total of these sales? In other words, I want to have a single workbook, containing 15 sheets, representing the total sales for variuos food items. Also, keep in mind please that I have a beginner level in excel. I really appreciate your cooperation. Thanks in advance
nouh (1 year ago)
Thank you Sir. I will check
Dinesh Kumar Takyar (1 year ago)
Search http://www.exceltrainingvideos.com
NCSUME1 (2 years ago)
Sub LoopThroughDirectory() Dim MyFile As String Dim erow MyFile = Dir("Z:\PM CEMS Workgroup\data\spreadsheets\") Do While Len(MyFile) > 0 If MyFile = "zmaster.xlsm" Then Exit Sub End If Workbooks.Open MyFile Range("J3:J22").Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.PasteSpecial , Transpose = True Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 20)) MyFile = Dir Loop End Sub
Dinesh Kumar Takyar (2 years ago)
Check out the code at this link: http://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
NCSUME1 (2 years ago)
This didn't work due to destination line.
NCSUME1 (2 years ago)
How would you do this and transpose the data during the paste function? That is, I have a number of different workbooks that have the data I'm after in a single column, but i want to generate a data base of each workbook by pasting that column data into a single row for each workbook?
Dinesh Kumar Takyar (2 years ago)
Have a look at this link: http://www.exceltrainingvideos.com/tag/copy-and-transpose-data-with-vba/ Or do a search at: http://www.exceltrainingvideos.com
Prakash Praaku (2 years ago)
This is truly wonderful video sir, Namaskara _/\_
Ryan Ryan (2 years ago)
Hello Sir i'm trying to copy data from many excel workbook to a master but get an error "ODBC Excel Driver Login Failed, External table is not in the expected format". However this error does not occur, if any of the workbook i'm copying from, is open in the background. i'm using normal macro and not VBA. the procedure goes like: DATA>GET EXTERNAL DATA>FROM OTHER SOURCES>FROM MICROSOFT QUERY when i refer to the located table it gives the mentioned error please guide. Regards
Fernando Salazar (2 years ago)
Hello, I am having trouble in the part of " Workbooks.Open (Filepath & MyFile)" Does it mean I literally just type "MyFile" or do I have to also put again the filepath and the name of my file? If anyone else can help with this I will greatly appreciate it.
Rome Hong (1 year ago)
Hi Sir. watched the above video. but what if i got many worksheets in one excel macro file? I need to just compile a specific worksheet say name "ABC" from few macro enable file with the same name to a master file. which are the macro code need to modify?
Dinesh Kumar Takyar (2 years ago)
This link will help: http://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
Neo XueLi (2 years ago)
hi Sir, I want to check with you that when I run the program, the data from different suppliers appears, but why it will still appear one more row showing the titles again?
Dinesh Kumar Takyar (2 years ago)
Check out this link: http://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/ Or do a search http://www.exceltrainingvideos.com
Paul Boot (2 years ago)
Hi Dinesh, thank you very much for this explanation, very useful. As a kind suggestion, I imagine some Excel users face the same issue I do, which is to copy data from various sheets into one consolidation sheet. Could this be worth another video? In any case, many thanks for this, very kind of you to take the time to record and upload!
Dinesh Kumar Takyar (2 years ago)
Have a look at this link: http://www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ A search on my website exceltrainingvideos.com might also help.
Wagner Cardozo (2 years ago)
Hi there, Thank you for your help... I have tried to adapt the code to my needs, but unfortunately it is not working I want to copy the cells from D8:P8 that are located in the SUMMARY worksheet in each workbook, and paste in to my "LME Master Log Summary" (in the Master Summary worksheet) Please help Ps: I am using Excel 2016 ---------------------------------- Sub LMEMaster() Dim MyFile As String Dim erow Dim Filepath As String Filepath = ThisWorkbook.Path MyFile = Dir(Filepath) Do While Len(MyFile) > 0 If MyFile = "LME Master Log Summary.xlsm" Then Exit Sub End If Workbooks.Open (Filepath & MyFile) Worksheets("SUMMARY").Activate Range("D8:P8").Copy ActiveWorkbook.Close erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("Master Summary").Range(Cells(erow, 1), Cells(erow, 4)) MyFile = Dir Loop End Sub
BigRyGuy04 (2 years ago)
Tap F8 through the code and it will tell which line has the error.
BigRyGuy04 (2 years ago)
What error are you getting?
Igor T (2 years ago)
Hi Dinesh, excellent work. Did find it quite helpful. I'm currently trying to put together a similar macro for my own use but when running it I do get a run-time error 1004 saying that xyz file could not be found. Macro gets the name of the first workbook right in the error message but either can't open it for some reason or cannot extract data from it (all data in sheet3). Have you posted any video to address this? thank you
Tareq Habjouqa (2 years ago)
Very helpful, thanks a lot for sharing!
khaleel ahmed (2 years ago)
I want to copy each value in a single column(G2:G215) from one workbook and paste to specific cell in multiple workbooks in the same folder. Example : value of G2(source file) in J16 cell of workbook1(target file) , G3(source file) in J16 cell of workbook2(target file). The target cell is same in all workbooks.
Raju J (2 years ago)
Dinesh sir very good video thank you
Farha Khan (2 years ago)
here i have a doubt if customer don't send the data in ordered format or columnnames as different then how can we insert
Farha Khan (2 years ago)
Hi sir hope you are doing well i got a task first sheet having all the field names with the length, start position, end position and column name of second sheet (like a,b,c...etc) and second sheet customer send the data only some fields(suppose i have 100 fields in sheet 1 customer will send the 50 fields data) we need copy the data based on column name in sheet 1 to another excel sheet can you please help me with the code i am new to excel macros. Thanks in advance
satya kurumanna (2 years ago)
Thank you sir for uploads.
Ruban Chakravarthi (2 years ago)
It doesn't work. I am getting an error message as unable to open workbook. Pls advise
iceant1000 (2 years ago)
Very educational sir. May i know your e-mail sir so that i will send a problem to solved. Thank you in advance.
Alin Avram (2 years ago)
Hi, please tell me how to do this if my Workbook contains much more data in it? For example I want to copy starting with A2 to AK462 (as the range) but this range is different from one file to another. Is it possible to do this with this code? Thank you in advance!
sudarsana sahoo (2 years ago)
i have also the same question
Fernando Salazar (2 years ago)
That´s a good question m8 I hope I can get to hear the answer.
Excel Sanyasi (2 years ago)
Dear Sir, I hope you are doing well. I have a query with, I have multiple worksheets more than 15 and i want to copy data from each worksheet, “sheet1” and paste that data in Consolidate data file in “Sheet1” that format in excel 2003. Can it is possible, i was use all suggested code which you are recommend in your website. Regards, Neeraj Jha
Dinesh Kumar Takyar (2 years ago)
http://www.exceltrainingvideos.com/how-to-consolidate-data-from-multiple-excel-workbooks-using-vba/
Swati Rathor (2 years ago)
I am using office 2013. I am getting run time error 1004 "sorry we could not find test1.xlsx. - is it possible it was moved renamed or deleted." However same code is working on my friend's PC. What could be the issue?
Swati Rathor (2 years ago)
+Dinesh Kumar Takyar​ yes. My sample data files are named as test1, test2 and so on
Nectaly Vela (2 years ago)
I got the same issue with 2013.
Dinesh Kumar Takyar (2 years ago)
Do you have a workbook with the name test1.xlsx?
Miguel Rossa (2 years ago)
Is it possible to insert a blank row on the zmaster sheet1 after copying the data from the sources? Thank you!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.