I have a macro up and running that sorts a pivot table, copies a range of cells out of the table and then pastes those into a second sheet. To be honest, with where I'm at with VBA right now I'm pretty happy with this feat alone. Yet I have more things I want it to do.
What I want to happen is this: The macro I have pastes data into the first empty cells in column A. When this happens I want the macro to enter today's date (preferably in a manner that makes it permanent and won't change to tomorrow's date tomorrow) in the same row in column C and the text "IV020" into column D.
In Sheet9 I have the following code (mainly taken from posts here):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error GoTo Whoa
Application.EnableEvents = False
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Target.Columns.Count > 1 Then
For Each aCell In Target
If aCell.Value <> "" And aCell.Offset(0, 2).NumberFormat = "" Then
aCell.Offset(0, 2).Value = "=TODAY()"
aCell.Offset(0, 3).Value = "IV020"
End If
Next
Else
MsgBox "Please paste in 1 Column"
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Yet when things are pasted or entered manually into Column A, nothing happens.
You cannot have a Range.NumberFormat property that is a zero-length string. Even if you tried to put one in manually, it would reset itself to General.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
'don't do things until you have to
On Error GoTo Whoa
Application.EnableEvents = False
Dim aCell As Range
'this processes all of the cells that were changes in column A
For Each aCell In Intersect(Target, Columns(1))
'If aCell.Value <> "" And aCell.Offset(0, 2).NumberFormat = "" Then
If aCell.Value <> "" Then
aCell.Offset(0, 2).Value = Date 'possibly Now but likely not "=TODAY()"
aCell.Offset(0, 3).Value = "IV020"
End If
Next
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
I've made some minor changes; you will have to decide what you want to do about the .NumberFormat issue.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments