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
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
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
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.
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
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?
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
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
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
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
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?
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
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.
On error resume next fixed the issue... kind of... But now the TMR macro copies the data from the workbooks and puts them on separate sheets... Why does that happen? Anyone ?
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.
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?
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.
Thank you for the prompt response, Dinesh. Yes, I typed 1(one). However, I'm trying with l(london) as well but still doesn't work. would appreciate if you could please just type this whole command for me again so I can verify. Thanks again for your help.
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.
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 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?
Use the lastrow concept: lastrow = Application.WorksheetFunction.CountA(Range(“A:A”)) Loop through the workbooks in the folder using the DIR function or FSO: www.exceltrainingvideos.com/how-to-edit-multiple-excel-files-in-a-folder-automatically/ www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/ www.exceltrainingvideos.com/tag/display-files-in-folder-with-vba/ www.exceltrainingvideos.com/tag/display-list-of-all-files-in-folder-in-combo-box/ www.exceltrainingvideos.com/copy-specific-files-from-folder-and-subfolders-into-destination-folder/
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?
Baurzhan Zhakeleyev Have you remarked this line of code like this: ‘Range(“A2:D2″).Copy You may have a look athis link: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Thank you Sir for video! I did not use this line of code. Instead I debugged and found that it copies range and even puts it correctly to destination file but then it goes to loops and deletes already put data in destination file. Then i tried to relocated mymasterfile to another directory and it worked. Many Thanks!
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! 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!!!
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.
Before copying and transferring the data, you can clear the data in the worksheet in which you are copying the data. This link will help: www.exceltrainingvideos.com/how-to-create-report-from-excel-data-sheet-with-vba/
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!
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?
There's a simple strategy for this situation: clear the data from the worksheet where data is to be copied again. Then copy/paste or transfer the complete data from the original worksheet again. This Excel VBA tutorial will guide: www.exceltrainingvideos.com/automate-search-display-print-archive-data-with-excel-vba/ Use a copy of your file and practice before using the original file and data!
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
+Nito Nascimento This link might help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
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
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.
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.
This link will help: www.exceltrainingvideos.com/copy-worksheet-from-another-workbook-rename/ In such a case it's a good idea to check whether the master file is already open. The above link will guide.
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
This link will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ A downloadable file is also available for practice.
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.
+ThePeterparker Gotcha, Why the heck it is deleting the data after collating the other file data into master file. We need to keep master file out of the folder in which the other files are situated, than it will not delete the data. And also in the code, the code is to read all types of files, we could change it to xls only than also it will not clear the data in the master sheet.
Hi, Did you find a solution for this? I'm having the same problem :(. I'm using the same code as Dinesh, but it paste the values and then clears it again.
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
Check your filepath carefully! This link will also help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Dinesh Kumar Takyar Actually it worked when the files were .xlsx i used this code: Filepath = FolderPath & "*.xlsx*" However, the files that contains the data to be copied is macro enabled. so when i try to use the code: Filepath = FolderPath & "*.xlsm*" Nothing happens. I tried the wildcard code: Filepath = FolderPath & "*.xls*" There was an actvity but no data was copied. Please help. Thank you.
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!
Dear Dinesh, I solved the problem. I have hungarian excell and VBA doesn't accept the Sheet1 as worksheet name neither in the erow and destination code. In the row of erow I have to use Munka1 and at the Destination the concrate name of the sheet called "dbszam".
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.
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
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
+Amos Samuel These links might help:www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/www.exceltrainingvideos.com/how-to-copy-data-from-one-excel-worksheet-to-another-and-print/Alternative you can do a search on www.exceltrainingvideos.com or my TH-cam channel.
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?
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 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!!
This link provides guidance: www.exceltrainingvideos.com/tag/transfer-completed-projects-data-to-another-sheet/ You may also do a search at www.exceltrainingvideos.com
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.
Copy and paste will not work. You can go to the following link and download a sample file: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
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
This link will help: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/ Or search www.exceltrainingvideos.com
This link will help: www.exceltrainingvideos.com/how-to-transfer-data-from-one-excel-workbook-to-another-automatically/ Else search www.exceltrainingvideos.com
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.
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!
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.
+Neha Rimmy Have a good look at these links: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
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
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 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.
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
Dear Sir, I have a question on VBAI have some forms in which headers are in First column and there scoring in second column.Say A1:A100 with headers and B1 to B100 with scorings. I used to receive so many files of same format in a data. I want to make a masterdump automated where all headers comes in row and scorings comes down , Say headers in A1 to AA1 and there respective scorings A2 to AA2( Infact to Transpose the data). Please help me to create a VBA for this.
Dear Sir, Thanks for the quick reply. I have checked the link before sending the email. Since I am not good in VBA coding need advice Let me make more clear In my file I have headers in B1 to B100 and their scorings in C1 to C 100. i used to receive multiple files like this in a day. Here I want to consolidate all my files to a master file in transpose.ie Header in A1 to say AT1 and theirs scoring should automatically come in down rows without opening these sheets. Please help
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.
There are a number of videos on this topic. Search my TH-cam channel and the play lists. You may also like to search my website: www.exceltrainingvideos.com
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?
Ben Febra Have a look at this link: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
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.
Use the MOD function to find the odd and even rows and then work as usual. Eg. if rowNum MOD 2 = 1 then MsgBox "The row number is " & rowNum & " odd." else MsgBox "The row number is " & rowNum & " even." end if
Dear Sir, kindly help me I am getting below error in a POP up when I click run macro and the first 'sub' line is coming yellow and 'folderpath' is coming red Sub copyDataFromMultipleWorkbooksIntoMaster() Dim FolderPath As String, Filepath As String, Filename As String FolderPath = “C:\work\excel_tutorial\suppliers\” POP up says below "Compile error: Syntax Error"
www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ Also, if you copy and paste the code from the website, it will not work. You need to correct the errors that happen during the copy and paste process.
Sir, I receive a Run-time error '424' Object required error on this line: erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row The full text of the routine is: Sub copyDataFromMultipleWorkbooksIntoMaster() Dim FolderPath As String, FilePath As String, Filename As String FolderPath = "C:\Users\dgray\Dropbox\Active\" FilePath = FolderPath & "*.xls*" Filename = Dir(FilePath) Dim lastrow As Long, lastcolumn As Long Do 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 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 15)) Filename = Dir Loop Application.DisplayAlerts = True End Sub Can you assist as to why I receive this error?
+Dinesh Kumar Takyar +David Gray Hi, I have tried your code and unfortunatelly it is not working: - error 424 appears on erow when the file is placed in folder different than the folder with source files ...I am not sure where this Dim erow as Long should be added - when I move the final file in the same folder as source files the 424 error does not appear (or any other), but nothing is pasted in the final file Any suggestions on this? Thanks in advance! P.S. The code I am using is: Sub TranferData() Dim FolderPath As String, Filepath As String, Filename As String FolderPath = "(here the correct path is inserted)" Filepath = FolderPath & "*.xls*" Filename = Dir(Filepath) Dim lastrow As Long, lastcolumn As Long Do 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 Dim erow As Long erow = all.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Worksheets("ALL").Range(Cells(erow, 1), Cells(erow, 4)) Filename = Dir Loop Application.DisplayAlerts = True End Sub
@@Exceltrainingvideos Thanks for your reply, I appreciate it. Your videos have been very helpulf for my university works. My idea was to do the same thing but in a folder located in Google Drive but it does not supports VBA. I have to write the code using Scrip Editor or something like that. However, I gave up this idea and now I I am thinking to download the folder, that contains all the files which I want to extract the data, directly to my computer. But the problem with this action is that the VBA code does not run because it can not find any file in the folder. Not untilI I go to the file, open it and finally save it in the folder which it is located. I am trying to write down a VBA code which do this action for all the files inside the folder, so that I do not have to go one by one opening and saving, so that my VBA code can extract the data works. Do you have a video that can help me with this?
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.
Sir, i have used your code to import multiple csv file in main worksheet and data was imported perfectly, but the problem is my data contains date in month wise and year wise , some of the dates were not exactly in same format as in source file some were dd/mm/yy and some were dd-mm-yyy kindly help me, to solve this
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
This link with a sample downloadable file will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Hello Mr, Dinesh, i am a beginner of xls VBA. After watching this video i tried the code. but when i run the code it says "Run-time error 424", Object required. can you help me out. i also want to know that if a source workbook have multiple "Tabs", can data from all the tabs be copied to another workbook? bellow is the code i tried. Sub copyDataFromMultipleWorkbooksIntoMaster() Dim FolderPath As String, Filepath As String, Filename As String FolderPath=”D:\MASTER\New folder\” Filepath=FolderPath & “*.xls*” Filename=Dir(Filepath) Dim Lastrow As Long, lastcolumn As Long Do 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=Worksheets(“ Data).Cells(Rows.count, 1).End(xlUp).Offset(1,0).Row ActiveSheet.paste Destination=Worksheets(“Data”).Range(Cells(erow, 1), Cells(erow, 24)) Filename= Dir Loop End Sub
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?
This link will help: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/ Or search www.exceltrainingvideos.com
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.
After the looping process and before the 'End Sub' you could write: ActiveWorkbook.SaveAs Filename:="C:\Users\YourName\Documents\test12345678.xlsm" _ , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Dear Sir, As per your instruction I am implementing the logic. The model run properly but in the master file respective data save 2 times ie each data replicate twice. please advice me.
siheang Hor There are no tricks. Just check your code and compare with my code step by step. Here is a link with a sample file that might help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Thank you Sir, Now I can make it work as your video. Before it can't find sheet supplier-a or b or c, after I delete this file and create new file now it work. Your video really useful thank you again!
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, Thanks for the tutorial. I have copied your code. But my data is different from yours. I have 2 columns in 1st 10 rows and in 11 to 100 rows i have like 36 columns of data and beyond that I have similar pattern of data. I am not able to use your empty row concept because its considering that I have only 2 columns for the entire data. So, though I am happy that it is able to copy 1st 2 columns only of all my data, I want to capture all my data. Please help. Thanks & regards Vaidehi
Hi Sir, Thank you for the reply. I am a Mechanical engineer with less background in coding. Could you suggest where can I get access to VBA functions and kindly help out. regards, Vaidehi
Got it Sir! You were right! I need to be cool in order to code. But I got from my friend that I was copying to left side but it is to be copied to right. Because in excel we type in values from left to right. The empty or last column would have to come to right side. lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToRight).Column VBA is exciting! Sir, My next question is how to write a code to calculate the average of the corresponding readings on an excel sheet. Sir, by corresponding I mean, I have added sets of data one below the other. Thanks & regards! Vaidehi
Hi Dinesh, 1st Query: Lets say our data is not organised in same format as master file than how do we copy data to correct columns? ex my workbook 1 has columns : NAME ADDRESS MOB_NO my workbook 2 has columns NAME MOB_NO ADDRESS EMAIL I want not only to copy data in organised manner in master file but I also want to include additional columns in master file which may not have been before. Also formatting may be different in workbook 1 and workbook 2, how will paste data in master file ensuring all the formatting is used from master file and not from source files. 2nd Query: I have a ticketing tool ServiceNow/remedy 8.0 in which I would have to login. After login I would like certain ticket data from certain period to be exported in excel. How can this be done through VBA? Thank for help and great already posted by you.. Regards, Vikram
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!
Thank you!
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
Great!
This is of a tremendous help for me. Thank you sir.
Excellent work sir!
Thank you for the step by step and reasons why. Your work is very much appreciated! 😊
You're the best Sir, thank you very much for your help !
Glad it helped!
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
Thanks for the video. It is very helpful. Keep up the great work
Hi
Very much appreciate your efforts....thumbs up
Hi Dinesh, wish you well. Is that possible to clear the Master old data before copying the updated data to? thanks a lot
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
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.
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
Was there a way to copy items from the sheet2 in a workbook? As opposed to sheet1?
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?
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
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
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
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
Sir, I am also having same problem.
Everything worked perfect, but I want to delete old data.
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?
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
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.
On error resume next fixed the issue... kind of... But now the TMR macro copies the data from the workbooks and puts them on separate sheets... Why does that happen? Anyone ?
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.
Thanks Dinesh. Like the Filepath=FolderPath & "*.xls*" section of code. This is what allows xls, xlsb, xlsm?? Correct?
krn14242 Yes!
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?
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.
Works fine!
Did you type 'l' (london) or '1' (one) in end(xlUp). 1 is incorrect.
Thank you for the prompt response, Dinesh. Yes, I typed 1(one). However, I'm trying with l(london) as well but still doesn't work. would appreciate if you could please just type this whole command for me again so I can verify.
Thanks again for your help.
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.
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
Please search the channel or my website www.exceltrainingvideos.com/
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?
Use the lastrow concept:
lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))
Loop through the workbooks in the folder using the DIR function or FSO:
www.exceltrainingvideos.com/how-to-edit-multiple-excel-files-in-a-folder-automatically/
www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
www.exceltrainingvideos.com/tag/display-files-in-folder-with-vba/
www.exceltrainingvideos.com/tag/display-list-of-all-files-in-folder-in-combo-box/
www.exceltrainingvideos.com/copy-specific-files-from-folder-and-subfolders-into-destination-folder/
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?
Baurzhan Zhakeleyev Have you remarked this line of code like this:
‘Range(“A2:D2″).Copy
You may have a look athis link:
www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Thank you Sir for video!
I did not use this line of code. Instead I debugged and found that it copies range and even puts it correctly to destination file but then it goes to loops and deletes already put data in destination file. Then i tried to relocated mymasterfile to another directory and it worked.
Many Thanks!
Hi Dinesh - how would you adjust the VBA code to PasteSpecial and include the formatting from the source workbooks? Thank you!
+Laura Brogan These links might help:
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
www.exceltrainingvideos.com/paste-special/
+Dinesh Kumar Takyar Thank you so much Dinesh!
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! 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!!!
Let me check
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.
Before copying and transferring the data, you can clear the data in the worksheet in which you are copying the data. This link will help:
www.exceltrainingvideos.com/how-to-create-report-from-excel-data-sheet-with-vba/
Before you run a macro always make a copy of the original file.
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!
+Hannah Sparks .. Someone please respond to this. I am facing the exact same problem.
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?
There's a simple strategy for this situation: clear the data from the worksheet where data is to be copied again. Then copy/paste or transfer the complete data from the original worksheet again. This Excel VBA tutorial will guide: www.exceltrainingvideos.com/automate-search-display-print-archive-data-with-excel-vba/
Use a copy of your file and practice before using the original file and data!
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
+Nito Nascimento This link might help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
+Dinesh Kumar Takyar , Thank you for your fast reaction.
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
+dweeds Someone please respond for this.
dweeds is
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.
Try this link: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/ or search www.exceltrainingvideos.com
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.
This link will help: www.exceltrainingvideos.com/copy-worksheet-from-another-workbook-rename/
In such a case it's a good idea to check whether the master file is already open. The above link will guide.
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
This link will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
A downloadable file is also available for practice.
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.
+ThePeterparker Gotcha, Why the heck it is deleting the data after collating the other file data into master file.
We need to keep master file out of the folder in which the other files are situated, than it will not delete the data. And also in the code, the code is to read all types of files, we could change it to xls only than also it will not clear the data in the master sheet.
Hi, Did you find a solution for this? I'm having the same problem :(.
I'm using the same code as Dinesh, but it paste the values and then clears it again.
Hi Peterparker, can you please help me if you have found the solution for this.
ThePeterparker , thanks a lot for your solution is working,
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
Check your filepath carefully! This link will also help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Dinesh Kumar Takyar
Actually it worked when the files were .xlsx
i used this code:
Filepath = FolderPath & "*.xlsx*"
However, the files that contains the data to be copied is macro enabled. so when i try to use the code:
Filepath = FolderPath & "*.xlsm*"
Nothing happens.
I tried the wildcard code:
Filepath = FolderPath & "*.xls*"
There was an actvity but no data was copied.
Please help. Thank you.
Do a search at www.exceltrainingvideos.com
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.
Use the LEN function: www.exceltrainingvideos.com/how-to-extract-specific-words-from-excel-database/
Perfect , that ones done :)
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!
Check your code carefully line by line. If you still have a problem then paste it here for more help.
Dear Dinesh,
I solved the problem.
I have hungarian excell and VBA doesn't accept the Sheet1 as worksheet name neither in the erow and destination code. In the row of erow I have to use Munka1 and at the Destination the concrate name of the sheet called "dbszam".
Great!
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.
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
Add tried this coding however it copies the data for column A and B only ... where i wish to copy it till Column N ...
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
+Amos Samuel These links might help:www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/www.exceltrainingvideos.com/how-to-copy-data-from-one-excel-worksheet-to-another-and-print/Alternative you can do a search on www.exceltrainingvideos.com or my TH-cam channel.
Great Information. Dear Sir how can we auto transfer data from Master work book to multiple work books. Please teach me.
Please can you show how to transpose the data on pasting into the summary sheet?
The link to this Excel VBA lesson will help: www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
Thank You So Much For this code
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?
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?
how to combine sheets with several names if we have more different files with different names
good help sir..thanks
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!!
This link provides guidance: www.exceltrainingvideos.com/tag/transfer-completed-projects-data-to-another-sheet/
You may also do a search at www.exceltrainingvideos.com
Thank you so much for the guidance!!
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.
Copy and paste will not work. You can go to the following link and download a sample file: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
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
This link will help:
www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Or search www.exceltrainingvideos.com
Is it posible to save it in another workbook where our macro is not present..if yes hw
This link will help: www.exceltrainingvideos.com/how-to-transfer-data-from-one-excel-workbook-to-another-automatically/
Else search www.exceltrainingvideos.com
Hi, how to use a dialog box to give a path to the folder instead of using folder path in the VBA script
Try an INPUTBOX.
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.
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!
Marie Smith Have a look at this link: www.exceltrainingvideos.com/how-to-copy-a-word-table-into-excel/
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.
+Neha Rimmy Have a good look at these links:
www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
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
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?
Run a 'for next' loop in the source book to clean up the data.
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.
How can you do the opposite? e.g Transfer data from a master workbook to multiply sub workworks based on values in certain cells?
I want this process in vise versa sir. Like single workbook to different workbooks
Use a looping process.
Hi Sir,
How to copy the entire row from multiple workbooks to master workbook when a condition is matched in column c
The link to this VBA tutorial will help: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
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
What code have you written till now?
+Dinesh Kumar Takyar
I didn't written any codes sir...
Dear Sir, I have a question on VBAI have some forms in which headers are in First column and there scoring in second column.Say A1:A100 with headers and B1 to B100 with scorings. I used to receive so many files of same format in a data. I want to make a masterdump automated where all headers comes in row and scorings comes down , Say headers in A1 to AA1 and there respective scorings A2 to AA2( Infact to Transpose the data). Please help me to create a VBA for this.
+Anish Babu This video might help: th-cam.com/video/-fFTW4If-Jw/w-d-xo.html
Dear Sir,
Thanks for the quick reply.
I have checked the link before sending the email.
Since I am not good in VBA coding need advice
Let me make more clear
In my file I have headers in B1 to B100 and their scorings in C1 to C 100. i used to receive multiple files like this in a day. Here I want to consolidate all my files to a master file in transpose.ie Header in A1 to say AT1 and theirs scoring should automatically come in down rows without opening these sheets.
Please help
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.
There are a number of videos on this topic. Search my TH-cam channel and the play lists. You may also like to search my website: www.exceltrainingvideos.com
How can we transfer data from multiple workbooks into multiple woorksheets in the same row/column of each worksheet of the master worksheet
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?
Ben Febra Have a look at this link: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
I Cant get the columns B & C imported into master file
Pls help
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.
Check your folderpath. It should be like so: FolderPath = “C:\work\excel_tutorial\suppliers\”
Do you have a '\' at the end?
nice macro veryuseful
how to copy different file from different folder and past in different folder as assign in excel
This link will help: www.exceltrainingvideos.com/tag/copy-specific-files-from-folder-and-subfolders-into-destination-folder-with-vba/
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 .
Use the MOD function to find the odd and even rows and then work as usual. Eg.
if rowNum MOD 2 = 1 then
MsgBox "The row number is " & rowNum & " odd."
else
MsgBox "The row number is " & rowNum & " even."
end if
Hi Sir, what if we have shape and I want to transfer to cells(( sheet) .. thanx
copy and paste a rounded rectangle shape:
Sheet1.Shapes.Range(Array("Rounded Rectangle 1")).Select
Selection.Copy
Sheets("Sheet2").Select
Sheet2.Paste
Dear Sir,
kindly help me I am getting below error in a POP up when I click run macro and the first 'sub' line is coming yellow and 'folderpath' is coming red
Sub copyDataFromMultipleWorkbooksIntoMaster()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = “C:\work\excel_tutorial\suppliers\”
POP up says below
"Compile error:
Syntax Error"
www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Also, if you copy and paste the code from the website, it will not work. You need to correct the errors that happen during the copy and paste process.
Thank you very much for your quick reply Sir,
sir I have not copied it from website.
I have created it word by word as you have shown in the tutorial
Sir,
I receive a Run-time error '424' Object required error on this line:
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
The full text of the routine is:
Sub copyDataFromMultipleWorkbooksIntoMaster()
Dim FolderPath As String, FilePath As String, Filename As String
FolderPath = "C:\Users\dgray\Dropbox\Active\"
FilePath = FolderPath & "*.xls*"
Filename = Dir(FilePath)
Dim lastrow As Long, lastcolumn As Long
Do 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 = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 15))
Filename = Dir
Loop
Application.DisplayAlerts = True
End Sub
Can you assist as to why I receive this error?
+David Gray Works fine. At present I can only suggest to define: Dim erow as Long
+Dinesh Kumar Takyar +David Gray
Hi, I have tried your code and unfortunatelly it is not working:
- error 424 appears on erow when the file is placed in folder different than the folder with source files ...I am not sure where this Dim erow as Long should be added
- when I move the final file in the same folder as source files the 424 error does not appear (or any other), but nothing is pasted in the final file
Any suggestions on this?
Thanks in advance!
P.S. The code I am using is:
Sub TranferData()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = "(here the correct path is inserted)"
Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)
Dim lastrow As Long, lastcolumn As Long
Do 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
Dim erow As Long
erow = all.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("ALL").Range(Cells(erow, 1), Cells(erow, 4))
Filename = Dir
Loop
Application.DisplayAlerts = True
End Sub
Hi, it is possible to do this but with Google Sheets?
Should be possible.
@@Exceltrainingvideos Thanks for your reply, I appreciate it. Your videos have been very helpulf for my university works. My idea was to do the same thing but in a folder located in Google Drive but it does not supports VBA. I have to write the code using Scrip Editor or something like that. However, I gave up this idea and now I I am thinking to download the folder, that contains all the files which I want to extract the data, directly to my computer. But the problem with this action is that the VBA code does not run because it can not find any file in the folder. Not untilI I go to the file, open it and finally save it in the folder which it is located. I am trying to write down a VBA code which do this action for all the files inside the folder, so that I do not have to go one by one opening and saving, so that my VBA code can extract the data works. Do you have a video that can help me with this?
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.
sheetx.activate
Help please...when I add ":=" after Destination, I get an error "... compile error: expected expression.... what can I do? 😣
Sir, i have used your code to import multiple csv file in main worksheet and data was imported perfectly, but the problem is my data contains date in month wise and year wise , some of the dates were not exactly in same format as in source file some were dd/mm/yy and some were dd-mm-yyy kindly help me, to solve this
watch the latest video: th-cam.com/video/-kz7lZEs538/w-d-xo.html
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
This link with a sample downloadable file will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Hello Mr, Dinesh,
i am a beginner of xls VBA. After watching this video i tried the code. but when i run the code it says "Run-time error 424", Object required. can you help me out. i also want to know that if a source workbook have multiple "Tabs", can data from all the tabs be copied to another workbook? bellow is the code i tried.
Sub copyDataFromMultipleWorkbooksIntoMaster()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath=”D:\MASTER\New folder\”
Filepath=FolderPath & “*.xls*”
Filename=Dir(Filepath)
Dim Lastrow As Long, lastcolumn As Long
Do 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=Worksheets(“ Data).Cells(Rows.count, 1).End(xlUp).Offset(1,0).Row
ActiveSheet.paste Destination=Worksheets(“Data”).Range(Cells(erow, 1), Cells(erow, 24))
Filename= Dir
Loop
End Sub
Hi Tareq,
You didnt add the
Application.DisplayAlerts=True
below Loop
Sir, I apologize for the many questions .. How can I get the last date on which the file was saved .. Thank you
This link will help: www.exceltrainingvideos.com/how-to-list-recent-files-with-vba/
Or search www.exceltrainingvideos.com
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?
This link will help: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Or search www.exceltrainingvideos.com
Can you please help in the same situation where i only need to copy "A2:Q2"
please help
This link will help: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
Use your row of data in the code.
How to copy file1 into sheet1 of master file and file2 into sheet2 of the master file please answer me .
+Neha Rimmy What is file1 and file2?
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.
After the looping process and before the 'End Sub' you could write:
ActiveWorkbook.SaveAs Filename:="C:\Users\YourName\Documents\test12345678.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Thank you sir for your valued openion
Dear Sir, As per your instruction I am implementing the logic. The model run properly but in the master file respective data save 2 times ie each data replicate twice. please advice me.
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?
siheang Hor There are no tricks. Just check your code and compare with my code step by step. Here is a link with a sample file that might help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Thank you Sir, Now I can make it work as your video. Before it can't find sheet supplier-a or b or c, after I delete this file and create new file now it work. Your video really useful thank you again!
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.
Share your code here.
Sir,
Thanks for the tutorial. I have copied your code. But my data is different from yours.
I have 2 columns in 1st 10 rows and in 11 to 100 rows i have like 36 columns of data and beyond that I have similar pattern of data. I am not able to use your empty row concept because its considering that I have only 2 columns for the entire data. So, though I am happy that it is able to copy 1st 2 columns only of all my data, I want to capture all my data.
Please help.
Thanks & regards
Vaidehi
First just be cool. Understand the code step by step. Then execute.
As an engineer you should be able to get the hang of VBA quickly. It's easy. It just requires some patience and step by step learning.
Hi Sir,
Thank you for the reply. I am a Mechanical engineer with less background in coding. Could you suggest where can I get access to VBA functions and kindly help out.
regards,
Vaidehi
Got it Sir! You were right! I need to be cool in order to code. But I got from my friend that I was copying to left side but it is to be copied to right. Because in excel we type in values from left to right. The empty or last column would have to come to right side.
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToRight).Column
VBA is exciting!
Sir, My next question is how to write a code to calculate the average of the corresponding readings on an excel sheet. Sir, by corresponding I mean, I have added sets of data one below the other.
Thanks & regards!
Vaidehi
Hi Dinesh,
1st Query:
Lets say our data is not organised in same format as master file than how do we copy data to correct columns?
ex my workbook 1 has columns : NAME ADDRESS MOB_NO
my workbook 2 has columns NAME MOB_NO ADDRESS EMAIL
I want not only to copy data in organised manner in master file but I also want to include additional columns in master file which may not have been before.
Also formatting may be different in workbook 1 and workbook 2, how will paste data in master file ensuring all the formatting is used from master file and not from source files.
2nd Query:
I have a ticketing tool ServiceNow/remedy 8.0 in which I would have to login. After login I would like certain ticket data from certain period to be exported in excel. How can this be done through VBA?
Thank for help and great already posted by you..
Regards,
Vikram
+Vikram Yadav This link might help:www.exceltrainingvideos.com/tag/automate-copying-of-data-from-columns-of-one-excel-worksheet-into-another/
How to write VBA code for converting date to word in other language like Kannada
Try this tutorial in Excel VBA: th-cam.com/video/RsyqqzholVk/w-d-xo.html