VBA - When a cell value changes, insert row and copy down the data

[jonny1984]

Hi

I'm sure this is relatively easy - I am just trying to create a VBA macro that anytime a cell's *value* changes (from an external data source) it will insert a new row and copy down the data

For example here is some data I have in a sheet where cells
B6 = Current Timestamp
C6 = Current Data

Whenever there is a change to Cell C6 (ie the data changes) ->> it should insert a new row and paste in the data so it would look like the example below

[TABLE="width: 171"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Current[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:35[/TD]
[TD]73[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Previous[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08:34[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]08:33[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD="align: right"]08:30[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]08:29[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]08:20[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD="align: right"]08:19[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD="align: right"]08:02[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD="align: right"]07:45[/TD]
[TD="align: right"]54[/TD]
[/TR]
</tbody>[/TABLE]

 

[Alan_P]

Hi Jonny,

Something like this should do the trick, needs to go in the sheet module;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$6" Then
    Rows("7").Insert
    Range("B7").Value = Range("B6").Value
    Range("C7").Value = Range("C6").Value
    Range("C6").Activate
End If
End Sub

Hope this helps,
Cheers,
Alan.

 

[jonny1984]

Hi Jonny,

Something like this should do the trick, needs to go in the sheet module;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$6" Then
    Rows("7").Insert
    Range("B7").Value = Range("B6").Value
    Range("C7").Value = Range("C6").Value
    Range("C6").Activate
End If
End Sub

Hope this helps,
Cheers,
Alan.

Hi Alan

Thanks for the above but will the formula above work if the cell C6 is being generated by a formula?

I thought worksheet_change only worked if a user changed the cell , versus if its constantly being re calculated by a formula - which requires the 'worksheet_calculate' function ?

But I couldnt get a code with that function to work!

 

[Alan_P]

Hi Jonny,

Sorry I didn't realise it was a formula...

You could try using a helper cell to track if cell C6 has changed since the last calculation, example below uses A1 as the helper cell. Initially you'd need to manually change the helper cell to match C6

Private Sub Worksheet_Calculate()

If Range("C6").Value <> Range("A1").Value Then
    Rows("7").Insert
    Range("B7").Value = Range("B6").Value
    Range("C7").Value = Range("C6").Value
    Range("C6").Activate
End If
Range("A1").Value = Range("C6").Value

End Sub

Cheers,
Alan