Audit Trail In Excel 2007

[sbecker61]

Hello, I posted this question before but I don't think I did a good job of explaining it. Sorry about that. here goes again.

Whenever a cell in range N30 to N(x) changes in a worksheet ab, I want the entire row copied to a new tab called "Audit". I also want a timestamp (YYYYMMDD HH:MM:SS AM/PM format) added so I know when the change occurred. I would like the timestamp to be the first column in the Audit tab. I would also like the data to be written to the first empty row in the Audit tab.

My code so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then Target.EntireRow.Copy Destination:=Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Problems I am having:

1) The number 14 just refers to the column N. How do I make it N30 and higher?
2) What code so I use to put the timestamp in the first column?

Thank you in advance.

Steve

 

[Ruddles]

1) If Target.Row >= 30 Then ...

2) Sheets("Audit").Range("A" & Rows.Count).End(xlUp) = Format(Now(), "dd/mm/yyyy hh:mm:ss")

Or format as "dd-mmm-yy hh:mm" - however you prefer.

 

[sbecker61]

Hi, I am getting errors using that method

1) I get "copy method of range class failed"
2) it does not copy a change to a new line. They all stay in the row.

My code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then Target.EntireRow.Copy Destination:=Sheets("Audit").Range("A" & Rows.Count).End(xlUp) = Format(Now(), "dd/mm/yyyy hh:mm:ss")
End Sub

Help !!!

 

[Ruddles]

Something like this:-

[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
 
[FONT=Courier New]If Target.Column = 14 Then[/FONT]
[FONT=Courier New] If Target.Row >= 30 Then [/FONT]
[FONT=Courier New]   Target.EntireRow.Copy Destination:=Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1)[/FONT]
[FONT=Courier New]   Sheets("Audit").Range("A" & Rows.Count).End(xlUp) = Format(Now(), "dd/mm/yyyy hh:mm:ss")[/FONT]
[FONT=Courier New] End If[/FONT]
[FONT=Courier New]End If[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

(Tested and working here.)

 

[sbecker61]

works great but it doesn't copy anything in column A of the row its copying. The timestamp does go into the 1st column but I don't get Column A of the row it is copying. Please advise.

 

[Ruddles]

What do you want in column A of Audit: column A of the source sheet or the timestamp?

 

[sbecker61]

In column A of the source sheet I have the company name. When I change the cell in Column N, the macro activates. everything in the source sheet row gets copied except for Column A (Company Name). In column A of the Audit (destination) tab I have the timestamp. Next is

 

[sbecker61]

sorry, I would like the Timestamp in column A THEN the entire row of the source sheet starting in column B.

 

[Ruddles]

[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Column = 14 Then
    If Target.Row >= 30 Then
      Target.EntireRow.Resize(1, Columns.Count - 1).Copy Destination:=Sheets("Audit").Range("B" & Rows.Count).End(xlUp).Offset(1)
      Sheets("Audit").Range("A" & Rows.Count).End(xlUp).Offset(1) = Format(Now(), "dd/mm/yyyy hh:mm:ss")
    End If
  End If
   
End Sub
[/FONT]

 

[sbecker61]

this code works great. Thank you so much for your help. I really appreciate it.

Steve