Using Current Date Function THANKS!!!

[amwilber]

Hello all!
I am attempting to use some sort of code that will take in account the current date to tell whether a certain row within my excel spreadsheet it older than 10 days. I am thinking it can be done with a simple "if" statement, but I do not remember how to incorporate the "today" part of it.

As you can see in the example below, I have multiple tickets that I need to track. I want to have some sort of code that will take these different rows and analyze if the Recieved Date (column M) is older than 10 days. If it is older than 10 days I would like it to copy and paste the entire row associated with that ticket into "sheet 2".

As well, I would also like to possibly take a look at the "status" (column L). If the row shows that the status is "sndl2", i would like it to copy and paste that row into "sheet 3"

THANKS IN ADVANCE!


EDIT: Deleted HTML image - Please use mock data only...No real names or data. Also note that your baby blue text is really hard to read. Moderator
<script language="JavaScript" src="%3Ca%20href=" popup.js"="" colo="" puremis="" sun="">www.interq.or.jp http:></script><center><table" target="_blank"></table">
THANKS SOOOOOO MUCH!!!!
</center>

 

[amwilber]

Anthony,
Thanks for all of the help,
I actually just found out what the problem was, for whatever reason my excel at work was not working properly...I threw this code into my comp at home and it worked great. I had our IT dept. re-install excel on my computer and it works just great. Thank you for all of the help throughout! It is people like you that make this website function at such a high level!!!

 

[amwilber]

One more thing however, how do i get the things to show up on a different row once they are copied to the second sheet? I want to include a heading on the second sheet but the copied material goes in the way...like I want it to start on row 3
thanks

 

[Anthony47]

You are welcome...
The macro start loading the data from the first empty line, searching from the bottom, but anyway "after line 1". If 1 line for the header is not enough, you should load the header before starting the macro.

Or you may amend the macro to:
-clear the destination sheet
-load the header row(s)
However this will prevent you from appending new data to the existing ones on the destination sheet.
If you like this logic, then use the following macro as a replacement of the previous one:

Sub Macro1()
DataSh="Sheet1"      '<<< Sheet with the source data
DestSh="Sheet2"       '<<< Output sheet; will be cleared!!
'
Sheets(DestSh).Select
Cells.ClearContents
Range("A1").Select
Range("Heads").Copy Destination:=ActiveCell
'
Sheets(DataSh).Select
'
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))  '<<< M2???
If (Date-Cell.value)>10 then
Cell.EntireRow.Copy Destination:=Sheets(DestSh).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

Modify the instructions marked <<< to reflect your spreadsheet organization; then somewhere (not in the output sheet..) write the headers (as many lines as you want, provided the first column is not empty) and assign to these cells the name "Heads".

Return to excel and run the macro; it will:
-clear what you defined the Output sheet (DestSh)
-copy the headers
-scan the data and copy according the previous logic.

Bye.