You can use this
Set sh = Worksheets("consol")


instead off

Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = Format(Now, "dd-mm-yy h-mm-ss")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Thanks Mike / Ron

Superb, don't know much about References but it works

One small change which you might assist with, instead of adding a new worksheet within the "consol" file how would I copy the
source files into the worksheet "Consol"?



"Ron de Bruin" wrote in message ...
Hi John

You forgot to set the reference
You must set a reference to the Microsoft ActiveX Data Objects 2.5 library

Download first the example zip to test



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source directory to "C:\MIS\Labour Module\Labour Import" but
it hits debug at below saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message ...
Ron DeBruin has some code examples that can do exactly this. I would recommend the ADO method that does not need to open the
workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message ...
I am looking for some simple Code that will open up all files in a certain Directory, then copy a range within a certain sheet
to one Workbook, then close all Workbooks except for the Consolidated one, thus consolidating all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet names, although each of these files will have a different
workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however this name changes each week, as I create a new file
for each week
The worksheet within my Consolidated file where all the file info wil be copied to will be called "Consol Info"


Thanks