Add multiple spreadsheets to single spreadsheet

Originally Posted by tmotommyo

Hi,

I have multiple spreadsheet files that I would like to combine into a single spreadsheet - with each individual spreadsheet as a worksheet. How can I easily do this?

Example:

File1.xls
File2.xls
File3.xls

Add all three files into NewSpreadSheet.xls with three worksheet tabs, File1, File2, File3.

I have tried importing, but this strips out all the formating in individual files, and cutting and pasting seems like a stone age and cumbersome way to do it.

I need to do this to lots and lots of individual files, frequently.

Help?

Thank you.

How many files in total?

Originally Posted by tmotommyo

Today I have 13 files - but tomorrow it could be more or less....

Just wondering, you had said lots and lots, can't have more than 256,
Place the sheets you want to transfer in it's own folder, and reply back telling us the complete folder address c:\mee\yyuu\whatever\
It doesn't have to, but it would be easier if you placed the receiving workbook into a different folder.
I'll check later to see if anybody has answered your question

Originally Posted by tmotommyo

c:\XLS_stuff

Hi tmotommyo,
Place this code in a workbook that is not in XLS_stuff\ folder
It would work best if each workbook only had one worksheet in it, if so once the code moved the worksheet to the workbook, it will close the workbook so you don't end up with mega workbooks open at once, I believe you are confused with the term worksheets and spreadsheets, anyway....

The manual way to move or copy a worksheet to another workbook is not with copy and paste but by:
opening both workbooks
selecting the worksheet you want to copy/move to the other workbook.
go to edit menu and select
Move or Copy Sheet......
select the workbook to move it to
then select the position in the workbook to move it to, in the code I selected position 1, just so I wouldn't have to come up with a variable for the last sheet,
The code loops through the workbooks in the folder and performs that same action as above,

I was not able to to get the sheet name to equal the original workbook name

Please Login or Register  to view this content.

Change Sheets("Invoice") to whatever the sheet name is
To the VBA experts who might be glancing at this, the code could use:
-A sure way of closing the workbook with the moved sheet
-A way to name the new worksheet the name of the it's original workbook