Copying a split range

[Horatio J. Bilge, Jr.]

I am trying to use a form to copy the formulas from a named range on one
sheet to a named range on a second sheet. The ranges are split, but they are
the same size and shape. For example range "Main" on Sheet1 is
"A1:A10,D110,G1:G10" and range "Backup" on Sheet2 is also
"A1:A10,D110,G1:G10"

Parts of the range copy correctly, but other parts copy the same formula to
a group of cells. For example, the A1:A10 portion may work correctly, but all
of the cells in the D110 portion get the formula from D1.

Here is the code I am using:
Private Sub cmdNext_Click()
If Me.optBackup.Value = True Then
Sheet1.Unprotect
With Sheet1.Range("Main")
.Formula = Sheet2.Range("Backup").Formula
.Locked = True
End With
Sheet1.Protect
End If
Unload Me
End Sub

 

[Dave Peterson]

Get each of the discontiguous areas separately.

Private Sub cmdNext_Click()
Dim myArea as range
If Me.optBackup.Value = True Then
Sheet1.Unprotect
for each myArea in Sheet1.Range("Main").Areas
with myArea
.Formula = Sheet2.Range(.address).Formula
end with
.Locked = True
next myarea
Sheet1.Protect
End If
Unload Me
End Sub

 

[Horatio J. Bilge, Jr.]

Thank you. I had to put the .Locked = True inside the With statement, but
otherwise, it worked great!

~ Horatio

 

[Dave Peterson]

Glad you caught the error.

Thank you. I had to put the .Locked = True inside the With statement, but
otherwise, it worked great!

~ Horatio