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
.