Fix to increment on Audit page Excel 365

[DThib]

Hello all,
I have the code doing what I want but cannot get the information that should paste over to the Audit Trail page table to add another row. It copies over a cell already containing previous data.
Help, please!!!

Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim ws As Worksheet

Set ws = Sheets("Audit Trail")

i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Target.Value <> PreviousValue Then
    With ws
        .Range("B" & i).Value = FormatDateTime(Now, vbShortDate)
        .Range("C" & i).Value = FormatDateTime(Now, vbLongTime)
        .Range("D" & i).Value = Environ$("username")
        .Range("E" & i).Value = ActiveSheet.Name
        .Range("F" & i).Value = Target.Address
        .Range("G" & i).Value = PreviousValue
        .Range("H" & i).Value = Target.Value
    End With
End If

End Sub

 

[DThib]

Any help?
I need to audit trail several sheets in a workbook for any changes. My code replaces the same cell of information. I cannot remember how to have the next available be filled.

 

[Joe4]

There probably isn't enough information contained in your post to help us debug your issue.

Can you answer/include the following?
1. What sheet module is this code found in?
2. Can you post the code for the "Previous Value" calculation?
3. Can you show us some sample data and your expected output?

 

[DThib]

There probably isn't enough information contained in your post to help us debug your issue.

Can you answer/include the following?
1. What sheet module is this code found in?
2. Can you post the code for the "Previous Value" calculation?
3. Can you show us some sample data and your expected output?

Hi,

I have it in 3 sheets I need to audit. So, the code is repeated 3 times.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
   PreviousValue = Target.Value
  
End Sub

Expected

From sheets with this setup

Manufacturer

Item

Model

Serial

Calibration Due Date

Wks before due

Interval (Months)

Current Tech

Last Tech

Temp. Possession

Out for Cal.

Returned from Cal.

Cal Turn around Weeks

Available? (in-house)

Not scanned

Replace

Calibration Source

Comments

Fluke Biomedical

Electrical Safety Test Meter

ESA 620

xxxxxx

7-Jun-2023

9

12

T. W.

31-Mar-2023​

​

No

Not scanned

Out for Calibration

Fluke Biomedical

Electrical Safety Test Meter

ESA 620

xxxxx

3-Aug-2023

17

12

D. G.

No

x

 

[Joe4]

I think I might see where you problem is.
You see to be using column "A" on your "Audit Sheet" to find the last row with data, but you only appear to be updating columns B-H.
You should use a column that will ALWAYS have data in it to determine where the last row of data exists.

So if you change this line:

i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

to this:

i = ws.Range("B" & Rows.Count).End(xlUp).Row + 1

see if that fixes your issue.

 

[DThib]

Thank you!

Dumb blonde move

 

[Joe4]

You are welcome.
Glad I was able to help.