Open all xls Files in folder & Copy To 1 Spreadsheet

I’m wondering if this is possible, if I have a folder with say 30 excel spreadsheets (.xls) all named differently (number of files will always change), can I easily write something in VBA to Open all the spreadsheets and copy each sheet over to an existing Excel spreadsheet? For example, have a ‘template’ spreadsheet where the VBA would exist, then have the first sheet, (Sheet1) of each 30 sheets be copied back over to the template.xls? Even better, could I rename each Sheet1 to the name of the file before copying it over? This would basically be the first step in my process of getting the spreadsheet made.


I found this code in a similar question, so how can I rename the Sheet to the opened file name, then copy that over to template.xls?

Please Login or Register  to view this content.

I use this macro to combine my xls files into one spreadsheet. Run this macro in a new workbook in the same folder as your files that you want to copy. Then just run it. It will open and copy all of the files and combine them into this new workbook. And you do not have to keep renaming your folder location. You just add this code to a blank workbook and you can save it as a template, by just renaming it each time. Or just delete the old data and run it again. You do not have to rename or change this code ever. Works great!

Please Login or Register  to view this content.

If you use Excel 2007 then just replace the references "A1:IV" and "A65536" with "A1:XFD" and "A1048576:.

Hi,

This is another option. It copies Sheet1 from every workbook opened. If there are several sheets and you need it to copy each one then you'll need another For..Next loop inside the one shown to cycle through all sheets and copy.

Please Login or Register  to view this content.

Hi,

Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Hi,

I realise that this will be OK provided there are no sheet names longer than 31 characters. It looks like Excel won't allow file names longer than this, hence the original truncation at 31 characters.

This second instruction could theoretically result in more than 31 characters. Hence it would be best if after this instruction you added a second.

Please Login or Register  to view this content.

Rgds

Hi,

First a point of clarification.

The existing code doesn't change anything in the file that's being copied. It merely opens it, temporarily changes the sheet name so that when the whole sheet is copied to the main workbook the sheet name is the name of the opened file. The 'daughter' file is then closed without saving, thus the original sheet name is retained. You make mention of changing sheet2. Does that imply that you want to permanently change the sheet2 name in the daughter file, or just the name of the sheet to which it's copied in the main workbook.

Then can you clarify this latest request.

Are you saying that you still want to copy over a complete sheet, but if the condition you mention is true, you want to update sheet 2 of the daughter file first, and then copy sheet 2 to the main. And is it JUST sheet 2 in this event or sheet 1 and 2?

On the condition.

Is there only ever one row occurrence of both 'Taxable Fixed Income' and 'Account' or could there be several or none?

If none then in that case do you still want to copy sheet1?

Finally where on sheet 2 do you want to copy the Taxable Fixed Income/Account row(s)

Rgds

Hi,

If I've understood correctly the following will copy either the TFI or Account row of sheet1 to A1 on sheet2 and then copy sheet 2 to the main workbook as before.

Please Login or Register  to view this content.

HTH

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

So yes, please start your own thread!