Help with 'simple' VBA code

[Beau the dog]

Hi,

New to VBA code and I'm trying to do something I'm sure you guys will find easy.

On my worksheet ("data Input") when cell H4 changes, I want the range in that worksheet (A4:AH4) to automatically copy (as value only) to another worksheet ("Archive2"). Each time this happens, I need it to copy this data on the next available row.

This is my effort...which doesn't work and I'm tired of YouTube now! The first Empty row on the Archive tab starts on A3

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("H4").Address Then

Range("A4:AH4").Copy Sheets("Archive2").Range("A2").End(xlUp).Offset(1, 0)

End If
End Sub

Please help...!

 

[Joe4]

Ok, so the cells being copied over contain formulas.
Do you want to keep them as formulas when copied over, or can we copy over just the values?

 

[Beau the dog]

Ok, so the cells being copied over contain formulas.
Do you want to keep them as formulas when copied over, or can we copy over just the values?

Just values is fine...thank you

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim rw As Long

    Set rng = Intersect(Target, Range("H4:H53"))
    
'   Exit if update not made in H4:H53
    If rng Is Nothing Then Exit Sub

'   Copy row updated in
    For Each cell In rng
'       Get row of update
        rw = cell.Row
        Range(Cells(rw, "A"), Cells(rw, "AH")).Copy
        Sheets("Archive2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    Next cell

End Sub

 

[Beau the dog]

This time we get an error message...please see attachments

 

[Joe4]

It ran just fine for me.
Do you have any merged cells or protected cells in your workbook?
Can you post a data example it is not working for?

 

[Beau the dog]

It ran just fine for me.
Do you have any merged cells or protected cells in your workbook?
Can you post a data example it is not working for?

Sorry that was my mistake, I had changed the Archive tab name. I've amended and it works fine. Thank you for your time.

 

[Joe4]

You are welcome.