Help! Where do I begin?

[Darcy]

I work with over 150 single sheet workbooks created from
the same template. I need to take data from 4 or 5 cells
on each sheet/workbook and make ONE spreadsheet on which I
can sort columns accordingly. I don't know what I need to
do. Pivot Table? Macro? Please help. This has been very
frustrating as when a customer needs (for example - all
engines with 3000-4000 cycles remaining) I have to go to
each sheet and look at that cell to find them - which is
very time consuming (opening 150 workbooks).

Thanks in advance,
Darcy

 

[Bob Umlas]

Something like this might be what you're looking for (Macro):
It assumes all the files you want to combine are in the same directory.
Put this code in a new workbook's VBE, then use file/open to get to the
directory containing these 150 files.

Sub Combine150WBCells()
Dim Result As Worksheet
Workbooks.Add
Set Result = ActiveSheet
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Range("A1:A5").Copy
Result.Range("A65536").End(xlUp).Offset(1).PasteSpecial
Workbooks(Dir(.FoundFiles(i))).Close False
Next
End With
Result.Rows(1).Delete
End Sub


HTH
Bob Umlas
Excel MVP

 

[CLR]

I have in the past created a similar "Executive Abstract Report" containing
300 links to 50 individual spreadsheets and it worked just fine......yours
would be somewhat larger but I see no reason why it would not work as
well......just create links between your selected cells and your Master
worksheet.......when the individual sheets change, the master changes.......

Vaya con Dios,
Chuck, CABGx3