Do you want to copy the values to some other location? Or do you want
to just remove the formulas and replace those with actual values
(returned by those formulas)?
If so, you don't need to copy at all. You can have a loop that:
- loops through every cell in the range of your interest
(forbidenRange)
- replaces the cell's .value with .value (i.e. it just puts its
visible value back into the cell as a value [as oppose to formula])

that's it.


On Jan 26, 2:40*pm, donh wrote:
On Jan 26, 2:30*pm, Alan wrote:





On Jan 26, 11:44*am, donh wrote:

Hi,

Excel 2003, little knowledge. *I'm using VBA below to protect my
worksheet as I have found that using the standard Protect Cells stops
me from using a scroll bar on the page. *I have now gone a little
further and wanted to Copy All and Paste Values to remove all formulas
so the page can be archived and outside refernce tables be changed
without affecting past information. *Of course I've now hit the
problem that I cant Copy All because I have the Range Forbidden
Active! *anu suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rgForbidden As Range

Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"),
Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"),
Range("K10:K56"), _
Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"),
Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"),
Range("B66:R70"), Range("B74:R78"), _
Range("B82:R86"), Range("B90:R94"), Range("B98:R102"),
Range("B106:R110"), Range("T10:U56"))

If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub
Range("A1").Select

End Sub

Many thanks

Don

Can you not copy/paste before you set the forbidden range?

A.- Hide quoted text -

- Show quoted text -

Hi Alan,

The way I've done it is the Range Forbidden is always active and lives
on the worksheet it applies to not in a module and with my limited
knowledge do not know how to turn it off and on if required

Don- Hide quoted text -

- Show quoted text -