Macro that captures change history question

[sspatriots]

I found this sample spreadsheet on line that has code in it to track changes on a separate worksheet called "LogDetails". This code only seems to work for one worksheet called "Data". I tried changing it to what you see below:

Dim sSheetName As String

'sSheetName = "Data"
sSheetName = ActiveSheet.Select

I thought this might allow the tracking to work for any worksheet in the workbook, however, not such luck. I'm guessing there is a lot more to this and I am only a novice at this stuff. Any help would be greatly appreciated.

Thanks,

Steve

 

[Joe4]

Try changing this:

sSheetName = ActiveSheet.Select

to this:

sSheetName = ActiveSheet.Name

 

[sspatriots]

Try changing this:

sSheetName = ActiveSheet.Select

to this:

sSheetName = ActiveSheet.Name

That seemed to work. Thank you.

 

[Joe4]

You are welcome.

Note that you only use "Select" when trying to select a range, not when you are trying to set the value of a variable.

 

[sspatriots]

Well, everything seems to be working with this code I found on the internet, except now I need to be able to grab information that exist in a few adjacent columns from the same row on this "LogDetails" worksheet. The code looks like the following right now:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String

'sSheetName = "2022"
sSheetName = ActiveSheet.Name

If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress

Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub

If I wanted to capture information from let's say a fixed column in the same row that is capturing the oldValue and Target.Value, for example using column H (8th column), how could I get it to grab that information for me?

Guessing it would start out something like:

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 8).Value = ?

Just don't know how to define that adjacent value in this Private Sub.


Thanks,

Steve

 

[Joe4]

That is an entirely different question and should therefore be posted in a new thread.

 

[sspatriots]

That is an entirely different question and should therefore be posted in a new thread.

I can do that. Just thought since it was the same code that the original question was coming from I could use the same thread. Thanks for the heads up.

 

[sspatriots]

Moved to new post entitled:

Need to expand on a macro that captures from and to changes onto a separate log​