Copying a split range
 

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

Copying a split range
 

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. wrote:

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

--

Dave Peterson

Copying a split range
 

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

~ Horatio


"Dave Peterson" wrote:

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. wrote:

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

--

Dave Peterson

Copying a split range
 

Glad you caught the error.

Horatio J. Bilge, Jr. wrote:

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

~ Horatio

"Dave Peterson" wrote:

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. wrote:

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

--

Dave Peterson


--

Dave Peterson