EXCEL VBA challenge . how to merge workbooks into one

Gurus,

i have a excel challenge and i tried asking some gurus here at my work, and No one was able to solve this.

i need a vba code to put in the workbook named MASTER

master workbook is located in C:/Myfiles

now there are multiple workbooks in the same directory and all of these workbooks have same numbers of identical sheets that has same names, same field structure; same column headings, and the same column order .

now the VBA code should combine all of these workbooks sheets into its related sheet into one workbook

for example lets say that there are 12 workbooks having 5 sheets sheet1 sheet2 sheet3 sheet4 and supposedly each of these worksheets has 10 rows of data excluding the header row.

when i merge them with VBA then my merged single workbook file will have sheet1 sheet2 sheet3 sheet4 each of these sheets in the merged file idealy should have 120 rows excluding the header row, becuase we had 12 workbooks.

the only addition would be that i need one new column added to the end of columns on each of the sheets in merged file that shows the original workbook name for those rows.

any help is appreciated

MASTER MERGED.xlsbCOMPANYB.xlsbCOMPANYA.xlsb


thanks for your help.

here is the attachments. the companyA and companyB files are the workbooks to be merged and MASTER MERGED is the example on how the end result should look like after the merger with VBA is done.

grateful for your help on this.

xladept

thank you very much.

i got error of Run-time error 9
subscript out of range in this line

Please Login or Register  to view this content.

do i run this code from Master Merged file or from another workbook?

also what are these colons : you used in your code? i have not seen this sort of VBA programming before.

i would be grateful if you could get this error fixed.

thanks.

xladept

thanks very much. i changed the Workbooks("MASTER MERGED") to Workbooks("MASTER MERGED.xlsb") adding the file extension then it worked.

thanks for clarifying the colon

i have a follow up question, some people use the _ to move to the next line in VBA.
then my question is which is most preferable and better to use the underscore or the colon as you used?

Hi,

I am trying to follow your example, but I am getting an error when I step through the code at

Please Login or Register  to view this content.

The error code is:

"Run-time error '5:

Invalid procedure call or argument."

Yes, I have U= DIR(P) in line 3