The range is always the row after the header (this is fixed on all sheets) till the last row with data on a specific column.
Now there are multiple sheets in each workbooks and all the worksheets will have the same name.
Sheets(1) 'I used index, you said there is only 1 sheet '~~ Put additional variable declaration Dim wb Master As Workbook, wb Temp As Workbook Dim ws Master As Worksheet, ws Temp As Worksheet Dim i As Integer File Path = "\AM01PCIFS01.global.root\Home Dirs$\yameen.sarwar\Desktop\Test\II1\" My Files = "\AM01PCIFS01.global.root\Home Dirs$\yameen.sarwar\Desktop\Test\II1\*.xlsx" My File = Dir(My Files) With Application . Display Alerts = False End With '~~ Open the file and at the same time, set your variable Set wb Temp = Workbooks. Sheets(i) 'I used index, you said there is only 1 sheet '~~ Now directly work on your object With wb Master. Name) 'This matches the sheet name in the source workbook to the sheet name in the target workbook erow = .
Open(Filename:=File Path & My File, Read Only:=True) 'Start the loop of sheets within the source workbook For i = 1 To wb Temp.
To have a closer look at this and a other merge tools for Excel, you are welcome to download 14-day trial version of the Ultimate Suite.
It is a lot easier to process data in a single file instead of switching between numerous sources.
For instance, you have three sheets data as below screenshots shown, now you want to consolidate the average, max or min value of each product in each month, how can you do?
Some of you may think that calculate them by yourself is quick, but pay attention, here I just take three sheets as instance, and how about calculate manually if there are hundreds of sheets?
All of the files have the same number of worksheets, worksheet name and the same structure when it comes to the header and such.
I am trying to consolidate the values of Excel 1, Excel 2 and Excel 3 to the Master file.I tried looking for this across the web but for my purpose, I have been unable so far to optimize the code required.