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]

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

to this:
Range("A4:AH4").Copy Sheets("Archive2").Cells(Rows.Count,"A").End(xlUp).Offset(1, 0)

 

[Beau the dog]

Thanks for the advice,

It ran it once, but won't run it again. It also didn't copy as value only.

 

[Joe4]

The code should run by itself automatically when the value in cell H4 is changed manually.
How are you changing the changing the value in cell H4?
Do you have any other VBA code?

 

[Beau the dog]

The code should run by itself automatically when the value in cell H4 is changed manually.
How are you changing the changing the value in cell H4?
Do you have any other VBA code?

I made the adjustment you mentioned and clicked run. It worked...I then tried to change the cell value by manually entering new data and it didn't work.
The value in cell H4 will always be a date (dd/mm/yyyy), and I'm only ever changing it to another date manually.

There are no other macros in the work book, however, when this works, I want to copy the macro for each cell H4 to H53!! Basically when they update any date in the cell range H4:H53...that row will copy to the archive.

Hope I've explained that clearly!

 

[Rick Rothstein]

I made the adjustment you mentioned and clicked run.

Your code is "event" code... you should not have to click "run" to make it work. I am thinking that maybe you have the code installed in the wrong code module. The code (with Joe's modification) should be installed in the worksheet's code module, not in a general module (where macros get installed). If you right click the tab for the worksheet that is to have this functionality and then click "View Code" on the popup menu that appears, the correct code module will appear. You should put the code in that code window... then it will work automatically.

 

[Joe4]

There are no other macros in the work book, however, when this works, I want to copy the macro for each cell H4 to H53!! Basically when they update any date in the cell range H4:H53...that row will copy to the archive.

To apply it to the whole section H4:H53, do it like 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)
    Next cell

End Sub

And make sure that you followed Rick's advice and have it in the correct place (I assumed that you already did, but maybe you don't).

 

[Beau the dog]

To apply it to the whole section H4:H53, do it like 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)
    Next cell

End Sub

And make sure that you followed Rick's advice and have it in the correct place (I assumed that you already did, but maybe you don't).

Thanks Rick and Joe.

I checked and the code was in the correct window, but I have now replaced it with the code listed above and it works brilliantly! Thank you so much.

Just one minor alteration, can we make it so it copies the cell value only and not the properties of the cells?

Thanks again...and Happy Easter!

 

[Joe4]

Just one minor alteration, can we make it so it copies the cell value only and not the properties of the cells?

What properties are you talking about, in particular?
Are there hard-coded values or formulas in the cells you are trying to copy over?

 

[Beau the dog]

What properties are you talking about, in particular?
Are there hard-coded values or formulas in the cells you are trying to copy over?

There are formulas in the coped cells with different cell colours, but I simply want 'not' to copy the cell colours and get rid of the bold text if possible. The data is coming through fine. I've attached a small screenshot.

I'd like it to come through as the top row.