Hi ,

let me explain what i have and what i want to do: i have a big file which
contains all my data for month like below:

A B C D E F
....
1 ID# PO# REF# P/N Disc Repaire Date ...
2 5645 d654d 65454 hdhd55 awkw 1/1/2010 ...
3 0980 wh32 54252 sk55ss skjss 1/1/2010 ...
4 0980 wh32 54252 sk55ss skjss 1/2/2010 ...
5 0980 wh32 54252 sk55ss skjss 1/2/2010 ...
6 0980 wh32 54252 sk55ss skjss 1/3/2010 ...
7 0980 wh32 54252 sk55ss skjss 1/3/2010 ...
..
..
..

i have daily worksheets named jan 1, jan 2, jan 3, ...
with the same fields.
what i want to do is:
write a code to open the big file and select all rows where their repaire
date are 1/1/2010 and copy them then open daily worksheet "jan 1" and paste
there then active big file and select all rows where their repaire date are
1/2/2010 and copy them then open daily worksheet "jan 2" and paste there and
so on...
i hope that i could explain good anyways thanks for your help
--
Farhad Hodjat


--
Farhad Hodjat

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

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

.