Copy down adjacent formuale using VBA (multiple columns)

[lucasreece]

Hi,

Looking for some help with the attached spreadsheet please...

What I'd like to do is when someone enters a value in column A, then copy down the formulae in columns C, E and H. So for row 2, when you enter a value, I need the formuale to copied down from C1, E1 and H1 to C2, E2 and H2 respectively (even if a row is missed e.g. blank row 3 and then value entered into A4, copy down formulae). Hope that makes sense.

The worksheet is protected to protect the formulae from being removed or changed so when someone enters a value in column A, the sheet needs to be unprotected (password is 'test'), formulae copied down and then sheet re-protected with the same password.

I can do this to a point and the attached file copies column C formula but not E or H. Please help. Also, if a line is missed it doesn't copy ANY formula. Again please looking for help with this.

Note: VBA code runs when worksheet is changed.

Many thanks.

Lucas.

test.xls

[pixifaery]

Hi Lucasreece

I've attached a spreadsheet which contains formulas (see sheet2). Rather than using VBA, I've locked the columns containing the formulas (format, cells, protection) and passworded the file with 'test'. This will allow the users to select columns in those cells but not to change them.

Is that what you wanted?

Regards
P

[lucasreece]

Thanks for your help and advice.

I ended up reordering the code in the worksheet change event and it now works...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
r = Target.Row
Select Case Target.Column
Case Is = 1
Call UnprotectSheet
Range("C" & r - 1).Copy Range("C" & r)
Range("E" & r - 1).Copy Range("E" & r)
Range("H" & r - 1).Copy Range("H" & r)
Call ProtectSheet
End Select
End Sub


Thanks.

[Cutter]

You mentioned in your original post that you wanted the formulas "copied down even if a row is missed". That being the case, your solution will not work for a row that follows a skipped entry in Col A because nothing is in the preceding row to be copied.

You would need to change:

Range("C" & r - 1).Copy Range("C" & r)

To:

Range("C1").Copy Range("C" & r)

And the same for E and H.

[mojo249]

Lucas

You need to be careful when you have a macro in the Worksheet_Change event which changes the sheet - because your macro will trigger itself so you get a looping effect. If you remove the error handler in your code you'll notice that it debugs on the line:

Range("E" & r - 1).Copy Range("E" & r)

This is your macro's calling of itself, not your own calling of it.

To get around this, you need to stop the macro from running after you've requested it - I've declared a public string (varCancelChange) in module 1 and added the extra lines. Perhaps others have a better way of solving the problem....

Dion

Private Sub Worksheet_Change(ByVal Target As Range)

If varCancelChange = "Yes" Then
    Exit Sub
End If

varCancelChange = "Yes"

    Dim r As Long
    
    On Error GoTo ErrorTrap
    
    r = Target.Row
    
    Call UnprotectSheet
    
    Select Case Target.Column
    
    Case Is = 1
        Range("C" & r - 1).Copy Range("C" & r)
        Range("E" & r - 1).Copy Range("E" & r)
        Range("H" & r - 1).Copy Range("H" & r)
    End Select

ErrorTrap:
    
    Call ProtectSheet

varCancelChange = ""

End Sub