Worksheet_Change Inquiry

[delulytric]

Hi, I'm a beginner in VBA and I'm not familiar with most of the code here. I read up on several threads about Worksheet_Change() and can't find something that suits to my need.

Currently, I'm doing a project and let's assume that I changed the price of fish from $2 to $4.

Sheet 1 (Original sheet)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Chicken[/TD]
[TD]$2.50[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fish[/TD]
[TD]$4.00[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I want it to transfer the entire row 3 Fish and store it into a historical data sheet for record keeping purpose. Bear in mind that for every change, it should automatically update itself through a new row! I would also want a date changed on column D so that it is understood when the last person did change the price or quantity of the row itself.

Sheet 2 (Historical data sheet)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[TD]Date Changed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Fish[/TD]
[TD]$4.00[/TD]
[TD]3[/TD]
[TD]15 March 2019[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fish[/TD]
[TD]$5.00[/TD]
[TD]3[/TD]
[TD]16 March 2019[/TD]
[/TR]
</tbody>[/TABLE]

I've search through the forum and sad to say I'm not very capable in handling VBA as opposed to my other projectmates. I only understood the start command Private Sub Worksheet_Change(), the end command and if intersect(target, range) is Nothing exit sub.

Any help here would be greatly appreciated here!

 

[Joe4]

Welcome to the Board!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Set range to watch for changes (column B)
    Set rng = Intersect(Target, Range("B:B"))
    
'   Exit sub if change is not in column B
    If rng Is Nothing Then Exit Sub
    
'   Loop through changed cells in column B (usually 1)
    For Each cell In rng
'       Capture row number of change
        r = cell.Row
'       Find next available row on sheet 2
        nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
'       Copy row from sheet1 to sheet2
        Sheets("Sheet1").Range(Cells(r, "A"), Cells(r, "C")).Copy Sheets("Sheet2").Cells(nr, "A")
'       Add date stamp to column D
        Sheets("Sheet2").Cells(nr, "D") = Date
    Next cell
        
    Application.ScreenUpdating = True
    
End Sub

 

[delulytric]

Thank you so much Joe4! I really appreciate the effort you put to help me out! Will try to read up on the code and try to understand it for future usage!

 

[Joe4]

You are welcome.
So, does it do what you want? They most important things in these Worskheet_Change event procedures is that they need to be placed in the proper location, in order to work automatically (in the proper sheet module).
If you have any particular questions about my code, feel free to ask! I always like helping people who show an interest in learning.

 

[delulytric]

Yups it does what I want but I need to wait for my projectmates to figure if they want any improvements. I think they will be able to improvise it further! Yeah, I played around with the sheet location directory, like sheet2 to sheet1, or sheet4 to sheet3 if sheet location is different. I get the code logic right there and learnt some new command personally haha.

Let's say if I want to record the previous data and current data (fish $2 previously, and fish $4 currently), do I need to declare a few more variables and keep track of like previous data and then copy the previous stored variables (fish, $2) first before copying the currently stored variables (fish, $4)? Haha.

 

[Joe4]

Let's say if I want to record the previous data and current data (fish $2 previously, and fish $4 currently), do I need to declare a few more variables and keep track of like previous data and then copy the previous stored variables (fish, $2) first before copying the currently stored variables (fish, $4)? Haha.

Yes, it gets a little tricky (you need to add Worksheet_SelectionChange to capture the value before you change it) If you do a Google Search, you should be able to find lots of posts on how to do that.