How to transfer multiple rows of data from multiple workbooks into master workbook with VBA 

Thank you so much Dinesh for the very useful tutorial. This is what I have searched for so long. All thumbs up to the best VBA teacher. Already subscribed. Hope everyone does the same

Dear Dr. Dinesh, I want you to know that I am very appreciative of your teachings. Thank you very much!

This is of a tremendous help for me. Thank you sir.

You're the best Sir, thank you very much for your help !

Thank you so much.. this has helped me to resolve a project which was incomplete..

Did anyone else have an internal panic attack at 15:05 for the next minute? Thank you for these helpful videos Dinesh

Hi Very much appreciate your efforts....thumbs up

Excellent work sir! Thank you for the step by step and reasons why. Your work is very much appreciated! 😊

I have learned so much from you and truly appreciate your taking the time to make these videos.Just an FYI Dinesh, for some reason, I could not get the "erow" to work and I have no idea why not. My four regional spreadsheets would simply copy over each other. I changed the erow to:erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row...and this worked perfectly. I also wanted to clear the data every time I ran macro. I added:Rows("2:" & Rows.Count).ClearContentsIf anyone cares, the script, only slightly modified, is;Sub CopyDataFromMultipleRegionalWorkbooksIntoGlobal()Dim FolderPath As String, Filepath As String, Fliename As StringFolderPath = "H:\data\Projects\Global_External_Resources_Management\RunBook-Data\"Filepath = FolderPath & "*.xlsx" 'this avoids macro sheets as I have a couple in the folder.Filename = Dir(Filepath)Dim lastrow As Long, lastcolumn As Long Rows("2:" & Rows.Count).ClearContents "deletes the content of the sheet minus headerDo While Filename "" Workbooks.Open (FolderPath & Filename)lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy Application.DisplayAlerts = False ActiveWorkbook.Close erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("Global_Data").Range(Cells(erow, 1), Cells(erow, 17))Filename = DirLoop End Sub

Thanks for the video. It is very helpful. Keep up the great work

How i can copy empty cell from worksheets, I have follow VBA code explained and looks like runs good but at the end of routine it leave me the sheet in blank information disappear. Thanks you are an excellent teacher

I watched another video of your and was very impressed. The video covered creating userform for putting in customers name, address and etc... I thought it was to long....Can we some how short that and transfer the information from one sheet to a second sheet, the second sheet is my invoice form. sheet one is multiform where all the other customer data is located.upon request I can send you both forms.Thank you

Was there a way to copy items from the sheet2 in a workbook? As opposed to sheet1?

Sir, is it possible to filter data on one column, copy that filtered data into new worksheet, again back to main sheet filter data with another item and copy that data to a new sheet. This process should go on until the data which is getting filtered gets over? Baciscally a loop on filter. Hope you have got the idea what I really want.

Hello Dinesh, many thanks for your great videos. I am a beginner, however, I need to do a similar thing to what you explained in this video. I want to be able to select my data based on the date. I am doing a stock report which is spread over several workbooks - however I can placed them in one book. I would want however, to create a monthly report. Please get me know how I can get this done

First of all, really appreciate your videos! Thanks a lot! I am trying to modify this particular example in order to copy a series of data (unspecified number of rows and columns) from different Excel files into a master file. Instead, I would like to paste the subsequent sets of data on the right, leaving an offset of two columns among each set. Please help. Thanks in advance

Hi Dinesh, wish you well. Is that possible to clear the Master old data before copying the updated data to? thanks a lot

Thank you so very much for explaining this so well. I'm not a VBA guy at all but I need this to work for me as it's something that I have been trying to accomplish for some time now. I went step by step and when I run the script (I made the alerts TRUE), I get the massage stating large amount of data, but when it finishes, the sheet is blank??? what could possible be wrong??? I have three workbooks with the last workbook being an .xlsm. Again, thanks

Very good Tutorial. Dear Sir im new in VBA Programming and i have a question how can i copy data from cells another wookbook to a 2 sheet of my master workbook.

Hello Sir, have seen your videos..they are awesome. I want a master file where it will paste data automatically using a single click by opening and closing the files in the same folder. The data is huge and contains date and time too. Can you pls help?

Great Information. Dear Sir how can we auto transfer data from Master work book to multiple work books. Please teach me.

nice macro veryuseful

Hello Sir! Thank you very much for your video, it was crisp, precise and easy to understand. I just have one query here. 1. Is it possible to paste the data and the formatting as in the original files? Like for example, the Row 5 of my first file may have its first three cells merged with data in it. So can the output file have the 5th row's first three cells merged with the same data? I appreciate the fact that the program you have explained respects filtering. Again, thank you very much for your efforts. Regards, Ajit.

Thank You So Much For this code

Dear Mr. Dinesh, Your Excel Videos are extremely helpful to solve some of the Real-Life Automation problems! Many Thanks for putting all your efforts to bring it up here. We are obliged ! I have a related query, If you may answer in any of your upcoming tutorial. Say in the Example above, If I wanted to do similar batch processing of files and in the Master I have a defined set of headers. Say - Item Name, Quantity, Price and Weight. Now In some of my suppliers files Only Three Headers are present say Item Name, Quantity and Weight and the other one has Item Name, Price and Weight and like wise. So I cannot copy by Range and Paste in the Master as the Headers would not match because some columns are missing in supplier files. Can we find a way to match Master column headers one by one to supplier column headers, Copy the matching header Column in Supplier worksheet and Paste it in the Master by Matching the Corresponding Header. Then loop through the Directory like in this example. Best rgds Satya

hi Dinesh sir, my question is how to copy a particular sheet3 from all the excel sheets to the master excel sheet. please help me, it is very uselful for me in office work.

good help sir..thanks

Thanks Dinesh. Like the Filepath=FolderPath & "*.xls*" section of code. This is what allows xls, xlsb, xlsm?? Correct?

Please can you show how to transpose the data on pasting into the summary sheet?

What if you need specific rows copied to a different worksheet/workbook, as in rows that that have a time marked in a column and have had a color fill to make that particular info stand out? Is there a way to have the program select only the rows that have been color filled and copy them to a separate workbook or worksheet?

Hi Dinesh, thanks for the video. I have 17 files in a folder that each have 9 tabs. I want range A5-B90 copied from each sheet and pasted into one master workbook. Can this be done?

What if I have data in sheet 2 and want to consolidate that as well in sheet 2 and also macro should go to different sheets to check if there is any data in the sheet 2,3 etc . If the data if found should copy and past in the respective sheets

Hello, I hope this will reach you. I like this code however is there a way that if I run this code again it will copy again the data and paste it on the bottom of the existing data?

very gud sir , but how to fetch even and odd rows of data from multiple sheets to a master sheet in a workbook depending upon various criterias .

Hi Dinesh I nee to know how to copy data from work sheet have common ID in both the 2 sheet but with different column names and copy to new master work sheet

Hi I have a simple quesiton... I need to select a specific sheet (Sheet2 in each of the workbooks in my folder) so how do i add this command to what i already have? I have the code working perfectly, except it's looking at sheet 1. Thanks!

Hi Dinesh, thank you very much for your vodeos, they really helpfull! I did everything as described, it looks like open excel file with rows but pastes only header line, how do you think what is the problem here?

Thanks ... Thanks ... Thanks

Thank you for this video, when i run the code i get an error on the Activesheet.paste line it says run-time error 1004 application-defined or object defined error, was hoping you could help, thanks

hi, I am new for Macro, and many many thanks for your educative videos, kindly assist me as i copied your formula, and try to run it is showing "Sub or function not defined.

How can you do the opposite? e.g Transfer data from a master workbook to multiply sub workworks based on values in certain cells?

thanks for g8 work sir

hi, i got multiple tabs in multiple workbooks to copy into a master..how do we do a loop to do that. The tabs all all named the same in each workbook..i do not need all tabs, only the same few from each workbook

Hello.. i just want to ask,what amendment should i make to the code so that it only copies the Last Used Row (the last row where data is filled) from all the workbooks present in the folder and paste it into the master file?

Dear sir, this tutorial is very helpful, but need to know if after running the VBA code today and the all the columns have been filled but later on another day I want to run the VBA code one more time and need the data to be added after the last data and and it should continue day by day . so what would be the script and also I do not want the previous data to get deleted.

Thank You sir

There are two main expected scenarios that should be considered so the solution will be perfect: 1- What if there are empty rows on the source file that I don’t want the application copy and paste. 2- What if there are more than 1 sheet that I want to copy and paste! I appreciate you reply.

how to copy different file from different folder and past in different folder as assign in excel

dear sir, I want to copy certain column from master file into my working file. I want to have the command in my working file and without opening my master file. can you help me on this

Hello Mr Dinesh, Can you help please!! I have to type some data( numbers) in 2 columns. Then I have to clear them and type some other ones, and again -again, several times. The problem I have is that every time I type these numbers, they must be transferred to another sheet in 1 column, one under the other ( Not overwrite), so that, at the end I have to find the Sum of them I hope you understand the problem, thanking you in advance.

Does anyone know of a way to do the reverse? Certain information from a master workbook is automatically sent to a corresponding worksheet depending on the information inputted into the master?

Hello Dinesh. Can you help me in coding for Copy multiple row of sheet 1 and paste it is non-blank row of sheet 2 within same excel sheet. I'm look to paste as special without disturbing the format of sheet 2. Thank you

Dear Sir, I hope you are doing well. I want to combine data from multiple workbook & multiple worksheet (each workbook has more than 2 worksheets) to master workbook in a single worksheet. Do you have any lecture above mentioned problem. Please advice me.

actually i have two workbook- data1.xlsx and data2.xlsx in C drive folder myprg, and i have one master file- mdata.xlsm and i want to copy data1.xlsx into sheet1 of mdata workbook and data2.xlsx into sheet2 of mdata workbook and i want to update mdata workbook automatically when i open the file.

Great Tute!, how can I copy the first 8 or 10 characters from each cell please. The 'Masterfile' will have a character restriction. Thanks in advance.

How can we transfer data from multiple workbooks into multiple woorksheets in the same row/column of each worksheet of the master worksheet

Dear Sir, Than you for your macro code. I am writing from Bangladesh. As per your instruction I establish a model for transfer data from multiple workbook to master workbook. The model run easily but can not save data in the muster file. Please advice me how to solution the problem.

Hi Sir, How to copy the entire row from multiple workbooks to master workbook when a condition is matched in column c

Add tried this coding however it copies the data for column A and B only ... where i wish to copy it till Column N ...

Hi Denish, My next challenge with extracting data from another workbook (back statement) is not looking for the first empty row (as you have taught us) but looking for all info up to a row that contains specific unique text that says "PRINCIPAL CREDIT" so my psuedo code is :lastrow = ActiveSheet.Cells(Rows.search for text = "PRINCIPAL CREDIT", 1).End(xlUp).Row Or is it in the definition of last row??:Dim lastrow As (text = ""PRINCIPAL CREDIT")Can you assist please? Kind regards Tim

I need to copy data from several workbooks, located in several folders in a server T:\. The sheet name is the same in all workbooks. The data that i need to copy is located in different cells. Could you help me?

Hello, File Re-open Issue: The code is working only when the Master file is open. If I close the Master file and tried to run macro again then I get error that could not be found despite file with the same naming convention and no change. Copy Specific Columns: How can I get only specific columns from the Source file to Master file by modifying the same program? Please advise.

Hello sir, I’m amazed with your website, but haven’t found yet a way to transfer multiple rows of data from multiple worksheets into one worksheet and export as PDF with VBA. I need this to send data to the clients of the company i work. I hope you can help me with this. Best regards João Nascimento

Hi! There´s a mistake in the file that I downloaded: after the code "erow..." there´s the lastcolumn code again. I erased it and it worked. Thank you very much! I´ve had a problem copying dates... When the month changes, it doesn´t paste the date that the original file said. I guess there´s a format problem. Any tips? Thanks again!!!

Thank you

is it possible to not have the workbook open? I have a userform that appears each time the source workbook is opened. This makes the process a little cumbersome. I'd like the code to pull the data without the source workbook being seen.

how to combine sheets with several names if we have more different files with different names

Hi Dinesh, Thankyou for explaining the code, however when I ran the code it is copying all the data from all the workbooks from the path & it ends up clearing the data from the master sheet. So i don't see data after running the macro in the master sheet. can you please suggest for it.

Thank you so much for taking time to do these videos. I've learned so much from you! I do have one question: is it possible when pasting the data into the master workbook to maintain the formatting from the copied sheet? Again, thank you!

I added ActiveWorkbook.Save as the data being pasted was not being saved so it overwritten each time a new file was opened and the data pasted. ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4)) ActiveWorkbook.Save

Hello Dinesh ji... I have been following your videos for quite sometime now and I find all of them very useful. In fact whatever VBA i have learnt, most of it is through your videos. Have few queries after watching this video....Hope you find time to help me on those - In my office situation, I have a Master files (approx.20) and other multiple files (saved in 1 folder) from which data is to be imported to the Master file, based on 1 key field. The file formats and field names are also different in each file but the key field is available in all files. Currently I am importing the data by doing a vlookup in my master file by opening each of the 20 files. This is a very time consuming activity. Is there a solution to this by automating it through VBA? Thank you once again for your highly educating videos

Hi and thank you for your videos, i was able to make the code work for the function copyDataFromMultipleWorkbooksIntoMaster(). But is there a way to delete the rows that were copied over from the source data unto the Master file? that way the data does not duplicate when the macros is ran. Also what code can I include to prevent source files from being updated if the file is in OPEN status. In other words the functions works perfect but i want to delete old data that has been moved over to the master and only if the source workbook file is closed. I appreciate your help allot. thanks

Hi, how to use a dialog box to give a path to the folder instead of using folder path in the VBA script

First off, these are VERY helpful tutorials!!! When using this macro, we can call it the "TMR macro", I open a few macro-enabled workbooks and the TMR macro times out or errors because of a "This workbook contains one or more links that cannot be updated" message that pops up after the first macro-enabled workbook is opened. I'm guessing that this can be corrected by an error handler... With that being said, is there a standard error handler to use or to I have to create one for different scenarios? If you (+Dinesh Kumar Takyar) or anyone has an answer please reply.

Is it posible to save it in another workbook where our macro is not present..if yes hw

How to write VBA code for converting date to word in other language like Kannada

Hi Dinesh, thank you for sharing! I'm trying to transfer data from multiple workbooks into one as shown by yourself in this video. However, it appears that there is an error with the below command: erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row I would appreciate if you could assist me on this as I'm new to Macro. Thank you.

Hi Dinesh - how would you adjust the VBA code to PasteSpecial and include the formatting from the source workbooks? Thank you!

I want this process in vise versa sir. Like single workbook to different workbooks

Nice vedio

Hi somebody pl help. I am able to copy and paste it into the master file. But after the macro runs the data is not reflected in the master. Checked using the immediate window it shows the data being pasted to master, but after the script runs, data is not reflected.

Hi Sir, what if we have shape and I want to transfer to cells(( sheet) .. thanx

Sir, In my case I have a file in which the input data need to be pasted every time from another platform SAP(ERP tool) . My Requirement is once I given input it should be saved automatically to the master file... The column Range is fixed and Row range varies... Kindly support

How to copy file1 into sheet1 of master file and file2 into sheet2 of the master file please answer me .

I can use this for my proyect but I need to clean up the data from the source books once the data had been transfered to the master data book. How can I do that?

Hi Sir, Thanks for the tutorial, I have 1 question. when I use this code Filepath = FolderPath & "*.xlsm*" nothing happens. the workbook that has the data is also macro- enabled. does it support macro enable workbook? Thanks in advance

I m Getting error as Syntax error in FolderPath, I don't know what I m doing wrong, Can someone help me, I have only changed the Actual File path

I Cant get the columns B & C imported into master file Pls help

How can I choose another worksheet in the files I am copying from? The sheet is "sheet 2". The data in the file starts in cell B3 and the last column is L.

Hello Sir, I am getting error with yello color high lighted" Compile error: sub or function not define", i have review all code but unable to solve it, please help to get this resolved.

sir, I tried using the code given in this video, but still code is not working as it through breaking error. I want to bring data from multiple sub folder to target path, how do I do that please explain

Help please...when I add ":=" after Destination, I get an error "... compile error: expected expression.... what can I do? 😣

I've add a snapshot from a file. So end of Mei I want to Copy the data from range "U5:U106", End of June I want to copy range "V5:V102" etc, etc, and copy this into antoher workbook. The problem is how can I pre-define the range based on a cell in the file? E.g.: Reporting Month: "mei-18" in cell "AA5" then the macro will select range "U5:U102" and copy. Or with a Msgbox. etc. Who can help?

Sir, I apologize for the many questions .. How can I get the last date on which the file was saved .. Thank you

Hey Dinesh, I have followed everything. After the paste values thing happened the macro is deleting the extracted data in the master workbook. How to address this?

Hello Dinesh.. i've tried the codes multiple times. However, its not working at all. Its not giving any error, It just does not run the function after ' Do While Filename "" ' I've pasted the master file in the same location where the rest of the files are, however it still doesn't work. Pleae help.

Dear Sir, I saw your video and it was very amazing. I tride to make my VBA code like yours but during the code run I had a big problem. Can you help me? The problem: my code doesn't use the activeworksheet so just put the datas the same xlsx file where these were coppied from. For example: I coppied the datas from workbook3.xlsx and after the code run the datas pasted the same workbook3.xlsx. I have no idea what is the problem. I hope you have. Thank you!

Hello and good morning. Wondering if you'd be able help me. I'm fairly new to Excel and mainly use it to track my labor to be submitted every week. I have a "Data Entry" worksheet, a "Measure" worksheet, a "Services" worksheet and a "Total labor" worksheet. On the "Data Entry" worksheet I enter the 'date' in cell "A", 'Customer name' in cell "B", 'Job Number' in cell "C", 'Product' in cell "D", 'Units installed' in cell "E" and 'Total' in cell "F". I would like to enter the information in the data entry worksheet and have it automatically transfer to "Measure" and "Total Labor" worksheets. The info from the "Data Entry" worksheet varies a bit from the "Measure" and Total Labor" worksheets as each worksheet is sent to different departments for processing. How do write a code to automatically transfer the info to the 2 worksheets in the next available line. Thanks in advance!!

Hi Sir, in the same way how do i post data from Active worksheet to Another worksheet without screenupdating , Excepting valuable regards and thanks in advance and kindly share your comments

I try to do exactly as what you show in video, exactly the same but it not working, Is there any trick that you not show?