combine Worksheet from different workbook in a single worksheet of a new workbook

I have different workbooks with different names but there is one common Worksheet with the same name in all the different Workbooks. I want to merge that common Worksheet from all the workbook in a single Worksheet?

Could anyone please help me with VBA code to solve thsi problem?

Thanks and regards
Nadim

Perhaps a code like this?

Please Login or Register  to view this content.

You need to change the sPath to the folder where the files are found and you also need to change the sheet name "Input" to the common worksheet name for all the files.

Alf

Thanks a ton Alf. Could you please help with one more thing that do I need to keep open all the workbooks from where the data is supposed to be copied? If yes, then there would be an issue that I have hundreds of such files and want the code to be run while the workbooks are closed.

Thank you once again.
Nadim

At the moment the macro checks all files in a specific folder and opens every file, one file at the time. Then macro tries to activates the appropriate sheet. If sheet is not found system genrates a number 9 error and closes the file. It then jumps down to to line "skipper:" and opens the next file and again tries to activate the specified sheet.

If sheet exists macro will copy all data from this sheet to ThisWorkbook Sheet1 (This Workbook is the workbook that contains this macro) The file is then closed and macro loops to check the next file in folder and will keep on doing so until every file is checked.

do I need to keep open all the workbooks from where the data is supposed to be copied?

No you don't macro will do that for you.

If yes, then there would be an issue that I have hundreds of such files and want the code to be run while the workbooks are closed.

???? I really don't understand what you mean. If the file don't contain that specific sheet macro will close the file immediately and open the next file. If this file contains specific sheet, sheet will be copied and then the file will be closed.

Alf