Actually, if they all were named with the same prepended text before the
date as originally presented, then they would be in order.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Sandy,

The easiest way to get them to open in date order is to use a sensible
naming convention, that is, use a date format of yyyymmdd, and put that at
the start of the file not the end.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sandy" wrote in message
...
Im not sure what I did but I do have a good copy now. However the code
is
not
opening the files in the correct orde. Files are named myfileyymmdd.xls
and
should open and copy in date order.
Thanks!

"Tom Ogilvy" wrote:

And we would know that how? So, yes, based on this new information -
it
should stay xlFormulas

You said it wasn't copying anything, so that was my best guess.
Otherwise,
it should copy as you described. Bob got the same impression I see.

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
The cells to be copied are populated with formulae, so shouldnt it
be
xlformulas?
Thanks

"Tom Ogilvy" wrote:

Change xlFormulas to xlConstants

--
Regards,
Tom Ogilvy


"Sandy" wrote in message
...

OK I changed sFolder = "C:\MyTest" to sFolder=ThisWorkbook.Path
And the code is opening the files. However it is not copying
the
range as
specified.
What did I do wrong?

"Bob Phillips" wrote:

Sub ProcessFiles()
Dim oFSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim this As Workbook
Dim iRow As Long
Dim oSh As Worksheet
Dim rng As Range

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
Set oSh = ActiveSheet
sFolder = "C:\MyTest"
If sFolder < "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=oFile.Path
With ActiveWorkbook
Set rng =
..Worksheets(1).Range("A337:A383").SpecialCells(xl Formulas)
iRow = oSh.Cells(Rows.Count, 2).End(xlUp)
If iRow < 1 Then iRow = iRow + 1
rng.Copy Destination:=oSh.Cells(iRow, 2)
.Close SaveChanges:=False
End With
End If
Next oFile

End If ' sFolder < ""

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sandy" wrote in message
...
Hi
I need to open all files in a specified directory and copy
the
contents of
A337:A383 ommitting every other cell (Blanks) and paste them
into
a
new
book
in a column starting in b1. The files are n ame
myfileyymmdd.xls
and I
need
to copy in date order
Thanks!