Record entire row with changes

[robbydogg]

Hello,

I have tried searching for the answer to this but can only find how to record the specific cell change.
I'm looking for a bit of code that records the data from the entire row which has had a change in it.

So if row 3 has a change made in column e, then i need the data from the whole row to be place in a log sheet (beneith and previous entries) and so on.

can anyone help me with this of if there is already code available ?

thanks in advance.

 

[Weaver]

right click the tab of the sheet you're making the changes on, choose 'view code' from the menu and paste the following. Make sure you set the reference to the log sheet as indicated

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("sheet2") 'change to the name of the log sheet
        tRow = 1
        On Error Resume Next
        tRow = .Cells.Find(what:="*", searchOrder:=xlByColumns).Row + 1
        Target.EntireRow.Copy .Rows(tRow).EntireRow
    End With
End Sub

 

[MickG]

Try this:-
This will save the Row data to sheet2, prior to the change, not the changed row data.

Option Explicit
Dim OldRng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       Set OldRng = Target.EntireRow
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not OldRng Is Nothing Then
With Sheets("Sheet2")
    .Rows(.Range("A" & Rows.Count).End(xlUp).Offset(1).Row).Value = OldRng.Value
End With
Set OldRng = Nothing
End If
End Sub

Mick

 

[Weaver]

D'oh!

Ignore mine - blame it on the liquid lunch, but there's not a whole lot of point logging the data after it's changed!

Good catch, Mick

 

[robbydogg]

Hi,

thanks for all your replies, but......

For some reason i can't get this to work.

I've copied and paste the code into the sheet VB code section and also tried in the 'ThisWorkbook' section


am i doing something obviously sillly?


cheers

 

[MickG]

Right click Data sheet tab, Select view code, VB window appears, Paste into window.
Make sure you don't leave the Vb Editer in "Design Mode".
Make sure you have not got two "Option Explicits".
Close VB Window.
Change a value in Data sheet, Click off the data cell to run the code. Sheet (2) should be updated.
Mick

 

[robbydogg]

Hi Mike,

I tried thes, but is still doesn't do anything. I have run macros on this version of excel before and they run ok (as security settings allow these).
I'm using 2007 if that makes any difference?

I also notice that the code is broken up into 3 sections tho - would this effect it's running do you think?

 

[robbydogg]

apologies - it does work - but it's replacing the log straight over itself.
any quick fix for it to past it on the row below?

 

[robbydogg]

and once again i realise it was me being the erroneous party.

got it working - thansk a million, you've been a massive help!!!

 

[MickG]

Try this altered code the old code, although working , had some problems.
If you can't get this to work I'll send you a file.

Option Explicit
Dim Ray As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    Ray = Target.EntireRow
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
With Sheets("Sheet2")
    .Rows(.Range("A" & Rows.Count).End(xlUp).Offset(1).Row).Value = Ray End With
End Sub

Mick