Audit Trail In Excel 2007

S

sbecker61

Board Regular
Joined
Dec 29, 2009
Messages
52
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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.
 
Upvote 0
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 !!!
 
Upvote 0
Something like this:-
Code: 
[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.)
 
Upvote 0
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.
 
Upvote 0
What do you want in column A of Audit: column A of the source sheet or the timestamp?
 
Upvote 0
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
 
Upvote 0
sorry, I would like the Timestamp in column A THEN the entire row of the source sheet starting in column B.
 
Upvote 0
Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Using XLOOKUP in VBA to lookup value underneath specific header name (columns can change depending on worksheet)
Replies
0
Views
163
Hailee994
H
S
  • Solved
BuiltinDocumentProperties - formatting
Replies
4
Views
173
RobP
R
A
  • Solved
count how many times text string appears in column
Replies
2
Views
222
ajm
A
D
  • Question Question
Define the previous value of a cell
Replies
2
Views
479
Diabentes
D
S
  • Question Question
I have a problem in audit trail with VBA
Replies
1
Views
387
Joe4
Joe4

Forum statistics

Threads
1,224,557
Messages
6,179,507
Members
452,917
Latest member
MrsMSalt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top