Copy & rename sheets across multiple workbooks into one master.

I have dozens of workbooks with multiple sheets per workbook, however I only need one sheet from each - ironically, always named "Sheet1." Is there a script that could copy all the sheet1's into one workbook, and (optimally) assign them the name of the value in a static cell, for instance C3? (the date is always in C3)

Otherwise, manually moving and renaming them using excel is rather tedious.

hello,

I thought and finaly created following code - I hope in excel 2010 it will work; I tested it under 2003 and works fine

Please Login or Register  to view this content.

maczaq:

I appreciate the code you've contributed greatly - however I don't quite understand what its doing. When I run it in a workbook, I get another sheet called "sheet1_old." It appears to be identical to sheet_1. I was looking for a macro to trawl all the other workbooks in a directory and take Sheet1 from all of them at once. Can this code be modified to pull from other workbooks? I apologize for not being more understandable!

Edit: it appears as if its combinining sheets within the workbook into one continuous sheet and renaming it sheet1 - I was looking for it to go through the directory, opening each document and searching for a sheet1 then compiling into one new workbook holding all of the sheet1s (named in any way would do.)

hi CapnSef,

previously I understand that you want to join data from all sheets in active workbook into one sheet.
So I created macro which executed following scenario:
1. check if "sheet1" exist
2. if yes then rename it
3. create new sheet 1
4. copy all data from sheets (excluding sheet1) and paste it in new sheet1

and that's all.

additional question: Am I right that You have some folder in your system where are many XLS files ? Each file have "Sheet1" and you want to open one file after another and copy all data from sheet1 directly to your main file workbook into sheet 1 ?

Hi again,

One problem is that you can not have many sheets in the same workbook called "sheet1" (in Polish Arkusz1 - presented in test file).
So I prepared example which copy all sheets from XLS files which are in the same directory as main.xls and after copy always rename copied sheet like (name of workbook + "_" + name of sheet in source workbook)

Try to test my example, I hope we are very close to your goal

[unzip all files into one directory on your hard drive and after open "main.xls" press the button]

whole code here:

Please Login or Register  to view this content.

hmm ... probable your workbooks / worksheets are protected ? (if yes copy method could be not possible)
My code should works under all version of Excel. Of course it is a bit difficult for me when I don't see your files to copy. Could you attach any source workbook (could be empty) because we copy whole sheets - content is not require.

Please Login or Register  to view this content.

If you put this macro into the workbook where all the sheets have to be assembled; run the code.
After that all sheets will be copied to this workbook
The workbooks that have been opened will be closed after copying.
The workbook the sheets have been copied into, remains open.

You can check the code using F8 in the vbeditor

I had hoped you to analyse what the code does and would have realized, that in she same folder in which the macro looks for files, also the open workbook resides. Opening that and closing at the same time isn't what we want.

Please Login or Register  to view this content.

That's what it's all about...