[Dave Peterson[_3_]]

I'd tell him to store them in a nice folder. Then when he wants to consolidate
them, run this macro:

Option Explicit
Sub BulkImport()

Dim InFileNames As Variant
Dim OutFileName As String
Dim fCtr As Long
Dim tempWkbk As Workbook
Dim consWks As Worksheet
Dim destCell As Range

Set consWks = Workbooks.Add(1).Worksheets(1)

InFileNames = Application.GetOpenFilename _
(FileFilter:="Excel Files, *.xls", MultiSelect:=True)

Application.ScreenUpdating = False

Set destCell = consWks.Range("a1")
If IsArray(InFileNames) Then
For fCtr = LBound(InFileNames) To UBound(InFileNames)
Set tempWkbk = Workbooks.Open(Filename:=InFileNames(fCtr))
tempWkbk.Worksheets(1).Rows(1).Copy _
Destination:=destCell
Set destCell = destCell.Offset(1, 0)
tempWkbk.Close savechanges:=False
Next fCtr
Else
MsgBox "No file selected"
End If

With Application
.StatusBar = False
.ScreenUpdating = True
End With

End Sub

He'll be prompted to determine the workbooks he wants to open. If he wants all
of them in the folder, tell him to hit ctrl-A when he sees the open dialog.

If he wants only a few, click on the first and ctrl-click (or shift-click) on
the rest.

Now some housekeeping:

Start a new workbook.
Hit alt-f11 to get to the vbe
hit ctrl-R to see the project explorer
Find your project.
It should look like: VBAProject (book1)
right click on it and choose insert|module
Paste that code into the right hand window.

Alt-F11 to go back to Excel
Save this file with a nice name.

Now back to excel.

View|toolbars
show the Forms toolbar
click on the icon that looks like a button.
Drag a nice big button on the worksheet. When you let go, you'll be prompted
for a macro to assign. Choose the one you just pasted in (bulkImport)

Edit the button's caption (and add some instructions so you remember what this
is used for on the worksheet).

When ever you want to import some workbooks, open this file, click on the
button and select your files.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Olly W wrote:

Hi guys
Ok i'm setting up a delay reporting system for products,
and it will be someone's duty to report on the delays, and
so for every delay, he will receive an attached
spreadsheet, which will be just one row with about 10
columns on info. He will receive roughly 30-50 a month.
Now, he needs to take each individual email he receives
with the individual reports (i.e one line spreadsheets),
and collate them into one monthly report.

How would be best to go about it-just simply get him to
copy and paste into a new workbook ? Or is there a simpler
way of going about this, to save time etc?

Any help greatly appreciated
cheers
olly

--

Dave Peterson