Importing Specific Cells from External Files

[Doig]

Hello

I have a folder where many excel files are stored. All of these files were created from an excel template. Each file created from this template was saved in the form of mm-dd-yy.xls (For example: 07-12-10.xls, 07-13-10.xls, etc..). All of these files have the same layout but just different information has been inputed. There is a specific sheet in each of these files that has a column of information that I would like to input into another spreadsheet so I could then sum up the whole months numbers. Is there a faster way of importing this data than opening each days file and copying and pasting the data into a new spreadsheet?

Please let me know if more detail is needed.

Much appreciated,

Doig

[teachesms]

I found a link on this @ http://www.vbaexpress.com/kb/getarticle.php?kb_id=829" onclick="window.open(this.href);return false; with the same question. Maybe it will help you.

OR

http://excel.tips.net/Pages/T002409_Mer ... books.html" onclick="window.open(this.href);return false;

[Doig]

Thanks for the quick response.

The second link worked for me. However, it imported all of the sheets from each file. It would be perfect if I could get it to just copy the cells from the specific sheet (which is the same name in every file) in each file.

Thanks again!

Doig

[HansV]

Welcome to Eileen's Lounge!

You can change the lines

Code: Select all

        Sheets().Move After:=ThisWorkbook.Sheets _
          (ThisWorkbook.Sheets.Count)

from the code in the second link to

Code: Select all

        Sheets("SpecificSheet").Move After:=ThisWorkbook.Sheets _
          (ThisWorkbook.Sheets.Count)

where SpecificSheet is the name of the sheet that you want to copy.

[Doig]

That worked perfectly! Thanks Hans!

From there, is there a quick way to sum up all of the values of a specific cell from each worksheet without selecting the cell from each worksheet copied over?

Thanks again.

Doig

[HansV]

Let's say that the first imported sheet is named SpecificSheet1, and the last one SpecificSheet15. To sum cell B3 over all these sheets, you can use the formula

=SUM('SpecificSheet1:SpecificSheet15'!B3)

[Doig]

The worksheets that were imported where as follows:

Supervisors, Supervisors (1), Supervisors (2),.....Supervisors (11)

When I type in =Sum('Supervisors:Supervisors(11)'!F11) it then gives me #REF! in the cell.
When I click back onto the cell, the fx=SUM('supervisors:[supervisors(11)]supervisors(11)'!F11) is displayed up top, where #REF! appears in the cell.

Any recommendations?

Thanks

Doig

[HansV]

You missed the space between Supervisors and (11). Try

=SUM('Supervisors:Supervisors (11)'!F11)

[Doig]

Wow! Sorry for the silly mistake!

Thank you so much Hans and teachesms for your help!

[HansV]

You're welcome! Feel free to come back and ask more questions.