Auto enter date

[Scott Marcus]

J. E. McGimpsey's code for date stamps works great. My question is, what if
I want to date stamp when only a certain value is entered, how would I do
that?

Here's the code with some of my specifics:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.NumberFormat = "mm-dd-yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

I'd like Column K to be date stamped only when the value of column F =
"Closed". With every other value, including blank, I'd like column K to be
empty, even if it previously had a value. Does anyone know how I can
accomplish this?

 

[Don Guillett]

What did you do with the answer I sent?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then
'Application.EnableEvents = False 'not needed
If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date
End If
'Application.EnableEvents = True 'not needed
End Sub

 

[Scott Marcus]

I tried it but it didn't work. It didn't give me any error message, it just
didn't input the date. I figured if I posted the entire code I started with,
it would be easier for you or someone else to help. Thanks again.

 

[Scott Marcus]

Actually, my original plan was totally different. I don't know if you can
help or not, but what I wanted to do is:

For every row in which cell F reads "Closed" I want the value from cells C
and J to form a list in another worksheet. I couldn't figure out how to do
this, so I settled for just date stamping the same worksheet and I'll have to
manually filter and sum the rows. Do you know if my original plan would be
possible?

Thanks,
Scott

 

[Don Guillett]

You should ALWAYS state your REAL desires so work is done once. Try this to
copy rows in sheet 1 with closed in col F to sheet 4

Sub copyautofiltered()
With Sheets("sheet1")
..Range("f1").AutoFilter Field:=6, Criteria1:="closed"
slr = .Cells(.Rows.Count, "f").End(xlUp).Row
dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1
..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _
Copy Sheets("sheet4").Cells(dlr, 1)
..Range("f1").AutoFilter
End With

 

[Scott Marcus]

After I made a few changes, I was able to get it to work once, then the
second time I pressed the button it didn't work. You must be pretty close,
though. I was hoping that I didn't have to create a button and that column F
could actually be the trigger to run the macro. When any cell in column F
changes to "Closed" it would copy just cells C and J from that same row to
the other worksheet. Here is what I ended up with:

Sub copyautofiltered()
With Sheets("Log")
..Range("F6:F9999").AutoFilter Field:=6, Criteria1:="Closed"
slr = .Cells(.Rows.Count, "F").End(xlUp).Row
dlr = Sheets("Fundings").Cells(Rows.Count, 1).End(xlUp).Row + 1
..Range("C6:J" & slr).SpecialCells(xlCellTypeVisible) _
.Copy Sheets("Fundings").Cells(dlr, 1)
..Range("f1").AutoFilter
End With
End Sub

Many thanks for all your continued help.
Scott

 

[Don Guillett]

Change de jour?
When you make up your mind, let us know.

 

[Don Guillett]

What you want is a worksheet_change event. You need not autofilter.

 

[Scott Marcus]

I didn't want to autofilter if it was avoidable. I posted my original
request three times but didn't get any answers, so I figured it needed to be
modified in order for anyone to help me. Sorry about the confusion. Let me
explain a little bit so you know exactly what I want to do.

I am creating a log for future use, meaning that unlike many other questions
posted by users, I'm not looking to sort through existing data. The log is
to be used pretty much all day everyday to keep track of changes/progress on
files. I am trying to minimize the amount of columns that are used and the
amount of data that needs to be entered, because the whole point of this log
is to make it easier for us. The log does have each entry's name, dollar
amount, and status (among other things). I also want to be able to keep
track of all the files which closed each month. At this point, I would have
to manually enter the date it closed, which of course requires another
column, then sort and select the ones from this month, then add them up. If
I wanted to know the names of the closed files, I would have to scroll back
to column C and then I couldn't see the dollar amount with it. For that
reason, I would like to be able to have the name from column C and the dollar
amount from column J to form a new log whenever column F reads "Closed". As
far as dividing it up by month, I haven't figured that out exactly yet. I
guess I could use a date stamp which will automatically put the date in when
a new entry as added to the "new log". I figured if someone sees this whole
book as a question, they will get scared off but you're right, it would
save time in the long run.

Thanks again,
Scott