How to loop through files in a directory and copy data into a master sheet in Excel?
Supposing there are multiple Excel workbooks in a folder, and you want to loop through all these Excel files and copy data from specified range of same name worksheets into a master worksheet in Excel, what can you do? This article introduces a method to achieve it in details.
If you want to copy specified data in range A1:D4 from all sheet1 of workbooks in a certain folder to a master sheet, please do as follows.
1. In the workbook you will create a master worksheet, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the code window.
Do Until xFileName = ""
1. In the code, “A1:D4” and “Sheet1” mean that data in range A1:D4 of all Sheet1 will be copied into the master sheet. And “New Sheet” is the name of the new created master sheet.
2. The Excel files in the specific folder should not open.
3. Press the F5 key to run the code.
Then a master worksheet named “New Sheet” is created at the end of current workbook. And data in range A1:D4 of all Sheet1 in selected folder is listed inside the worksheet.
How to copy cells data with row height and column width in Excel?
Hi, thanks for the code. Please can you let me know how I can include the Excel file name from which the data range was copied? This would be a great help!
thank you for the vba code! It works perfectly! Would like to know what is the code if i need to PASTE AS VALUE instead? Thx in advance!
The following code can help you solve the problem. Thank you for your comment.