Copy cells from same row as a given cell

[Mathilde]

Hi,

I'm pretty new to VBA and need to solve the (probably) easy problem below:

I have an array (called OldData), that contains the addresses of cells. When the sheet is saved, it compares the old values with the new ones. If the value has changed, I need to copy the value of this cell, and 2 other cells in the same row into another worksheet (called Log).
To make it more simple, I tried to just copy the whole row in the new sheet, but that doesn't work either.
Also, as my cells contain formulas, I need to use some kind of "paste special", to copy the actual values, but couldn't find anything about that in the Excel VB help.

Thanks a lot for any help,

Mathilde



Private Sub Workbook_Open()
Dim r As Range, n As Long
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
End With
With Sheets("New materials list").Range("K4:K1000")
For Each r In .Cells
n = n + 1
ReDim Preserve OldData(1 To 2, 1 To n)
OldData(1, n) = r.Address: OldData(2, n) = r.Value
Next
End With
With Application
.Calculation = xlAutomatic
.EnableEvents = True
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Long
For i = 1 To UBound(OldData, 2)
If Worksheets("New materials list").Range(OldData(1, i)).Value <> OldData(2, i) Then
Worksheets("Log").Range("A1").EntireRow.Insert
Worksheets("New materials list").Range(OldData(1, i)).Rows.Copy _
destination:=Worksheets("Log").Range("A1")
End If
Next
End Sub