Thanks Joe! i don't know where are you locared. i am in Toronto and it is
12:30 at night so i will try your code tomorrow at work. thank you again for
your help
--
Farhad Hodjat


"joel" wrote:


I assumed the data was in the 1st tab in the big workbook and I put the
data into the destination workbook starting at the first new row in each
sheet.

Sub GetDailyData()

FileToOPen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If FileToOPen = False Then
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=FileToOPen)

Set Sourcesht = bk.Sheets(1)

With Sourcesht
'sort by date then ID
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("F1"), _
order1:=xlAscending, _
key2:=.Range("A1"), _
order2:=xlAscending

OldDate = ""
For RowCount = 2 To LastRow
NewDate = .Range("F" & RowCount)
If NewDate < OldDate Then
StartRow = RowCount
OldDate = NewDate
End If

NextDate = .Range("F" & (RowCount + 1))

'copy rows from big book to this workbook
If NewDate < NextDate Then
Set CopyRange = .Rows(StartRow & ":" & RowCount)

ShtName = Format(NewDate, "MMM d")
With ThisWorkbook.Sheets(ShtName)
'Get LastRow on sheet
NewLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
NewRow = NewLastRow + 1
CopyRange.Copy _
Destination:=.Rows(NewRow)
End With

End If

Next RowCount


End With


bk.Close savechanges:=False

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169897

Microsoft Office Help

.