Need to save data in other sheet

[tusharkaushik]

Hi,

I have this code which helps me in saving data for two cells in different sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("M2").Address Then
Dim intLastRow As Long
intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
Sheet2.Cells(intLastRow + 1, "B") = Target.Value

ElseIf Target.Address = Range("M3").Address Then
Dim intLastRow2 As Long
intLastRow2 = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
Sheet2.Cells(intLastRow2 + 1, "B") = Target.Value
End If
End Sub

With this code working i want to save the data for the range P2:P7 and AC2:AC7 in different sheet. They should be saved in different columns (Like P2 data goes to C, P3 data goes to D) so that i can do comparison later.

Thanks in Advance.

[#2 · Accepted solution]

@tusharkaushik 

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim c As Long
    Dim r As Long
    If Not Intersect(Range("M2:M3,P2:P7,AC2:AC7"), Target) Is Nothing Then
        For Each rng In Intersect(Range("M2:M3,P2:P7,AC2:AC7"), Target)
            c = rng.Row - 1 - 2 * (rng.Column = 16) - 8 * (rng.Column = 29)
            r = Sheet2.Cells(Sheet2.Rows.Count, c).End(xlUp).Row
            If rng.Value <> Sheet2.Cells(r, c).Value Then
                Sheet2.Cells(r + 1, c).Value = rng.Value
            End If
        Next rng
    End If
End Sub