Copying a split range

  • Thread starter Horatio J. Bilge, Jr.
  • Start date
H

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,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
 
D

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
 
H

Horatio J. Bilge, Jr.

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

~ Horatio
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top