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,D1:D10,G1:G10" and range "Backup" on Sheet2 is also
"A1:A10,D1:D10,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 D1:D10 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