HELP !! Macros to autoexecute on open
 

Hello I have a task list that are scheduled on different days. I need help
for a code which will short list task due for the current date and/or not
completed from previous days.

My data are as follows
A B C D
Job No Employee Date_due Status
2501 John 03/16/07
2505 Trish 03/23/07 OK
2518 Paul 04/03/07

Upon opening the workbook It would be great if Job 2501 and 2518 could be
shortlisted say on sheet2, whereas sheet1 would contain the above list.

Anyone can help

Thank you your usual coorperation

HELP !! Macros to autoexecute on open
 

Use the code below. I did it as a regular macro (not worksheet_open) to make
it easier to debug.

Sub checkstatus()

'get last row of column a
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'get range of sttatus column d
Set RangeColD = Range(Cells(2, 4), Cells(LastRow, 4))

RowCount = 1
For Each cell In RangeColD

If cell.Value < "OK" Then

If cell.Offset(Rowoffset:=0, columnoffset:=-1) < Now() Then

cell.EntireRow.Copy _
Destination:=Sheets("sheet2").Cells(RowCount, 1)

RowCount = RowCount + 1
End If


End If

Next cell

End Sub


"Wins07" wrote:

Hello I have a task list that are scheduled on different days. I need help
for a code which will short list task due for the current date and/or not
completed from previous days.

My data are as follows
A B C D
Job No Employee Date_due Status
2501 John 03/16/07
2505 Trish 03/23/07 OK
2518 Paul 04/03/07

Upon opening the workbook It would be great if Job 2501 and 2518 could be
shortlisted say on sheet2, whereas sheet1 would contain the above list.

Anyone can help

Thank you your usual coorperation

HELP !! Macros to autoexecute on open
 

Thanks this will definitely help.


"Joel" wrote:

Use the code below. I did it as a regular macro (not worksheet_open) to make
it easier to debug.

Sub checkstatus()

'get last row of column a
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'get range of sttatus column d
Set RangeColD = Range(Cells(2, 4), Cells(LastRow, 4))

RowCount = 1
For Each cell In RangeColD

If cell.Value < "OK" Then

If cell.Offset(Rowoffset:=0, columnoffset:=-1) < Now() Then

cell.EntireRow.Copy _
Destination:=Sheets("sheet2").Cells(RowCount, 1)

RowCount = RowCount + 1
End If


End If

Next cell

End Sub


"Wins07" wrote:

Hello I have a task list that are scheduled on different days. I need help
for a code which will short list task due for the current date and/or not
completed from previous days.

My data are as follows
A B C D
Job No Employee Date_due Status
2501 John 03/16/07
2505 Trish 03/23/07 OK
2518 Paul 04/03/07

Upon opening the workbook It would be great if Job 2501 and 2518 could be
shortlisted say on sheet2, whereas sheet1 would contain the above list.

Anyone can help

Thank you your usual coorperation