Hi Incidental,

I think that Nav was seeking not to open the source files.


---
Regards,
Norman



"Incidental" wrote in message
oups.com...
Hi Nav

you could try something like the following which will open each excel
file in a given directory and copy all the data in the first four
columns (except the header) and paste it in the workbook running the
code. it will then save a copy of the workbook in the desired
folder. Then close the workbook you ran the code from without saving.

add a module to a workbook then paste the following code into that
module, you can then trigger the macro by a keyboard shortcut or a
button ect.

Option Explicit
Dim MyFile As String
Dim MyWkBk As String
Dim Directory As String
Dim LstCell As String

Sub GetMyData()
MyWkBk = ActiveWorkbook.Name
Directory = "C:\Test\" 'change this to the directory for your files
MyFile = Dir(Directory & "\*.xls")
Do Until MyFile = ""
Workbooks.Open (Directory & MyFile)
LstCell = [A1].End(xlDown).Offset(0, 3).Address
Range("A2", LstCell).Copy
Workbooks(MyWkBk).Activate
If [A2].Value = "" Then
[A2].Activate
Else
[A2].End(xlDown).Offset(1, 0).Activate
End If
ActiveCell.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Workbooks(MyFile).Close (False)
MyFile = Dir
Loop

ActiveWorkbook.SaveCopyAs "S:\data\combined" 'this directory must
exist or it will give an error
End Sub


hope this gives you an idea of what to do

S