Displaying data from multiple spreadsheets into one spreadsheet.

[Robin]

Hello

I am new to using macros so i need some help to create a macro that copies
data from a bunch of spreadsheets(about 20) and displays the data in one
spreadsheet. Also is it possiable to do this without actually opening the
spreadsheets that I want to copy the data from?

Thanks
Robin

 

[Joel]

Do you meand spreadsheets or workbooks?

Speadsheets can be copied within one workbook without activating each sheet.

Workbooks have to be opened to copy the sheets, but the original workbooks
can be closed without altering the data or the data of the workbook.

 

[Robin]

oops, srry i meant workbooks. also the workbooks are all in different folders.

 

[Joel]

two more questions

1) Allworksheets, activeworsheet, or specific names
2) All sub-folders under a root directory? Some people put the names of
each file in the worksheet in a column, then opens each of the file name
using the worksheet. Do you need all *.xls files or using a filter (looking
for name starting with or contain some string)?

 

[Robin]

1) Allworksheets.
2)nope, all folders are under different directories and i would be using a
filler.

thanks for your time Joel

Regards
Robin

 

[Ron de Bruin]

Hi Robin

I have code and a add-in on my site that you can try
http://www.rondebruin.nl/tips.htm

See the "Copy/Paste/Merge examples" section

 

[Joel]

Did Ron help you? I can modify any of his examples if necessary. I think if
all the folders are in random locations you probably want to put the
directories on a worksheet in your summary workbooks. You can call the sheet
Summary Folders.

The code can take each of these directories and then search for all files in
the directory such as abc*.xls. then copy all the workssheets in these book
to the summary sheet.

 

[Robin]

thanks for ur time joel. i will try ron's codes and see if they help.

 

[Robin]

hi ron

u really have some good stuff at ur site. i will try out ur codes right away.

 

[Robin]

hello again,
well i have looked through ron's examples and what u said abt puting my
directories on a summary worksheet seems like a really great idea.

so i was wondering if u could tell me in more detail as to how i can
integrate the summary workbook with the macro(i am thinking abt using the
"MergerwithAutoFilter" macro from Ron's examples).



Thanks and Regards
Robin