Is the code you posted a macro that you run manually on the NEW LIABILITIES
sheet?

Post the entire code between Sub mymacro() and End Sub

Don't forget that you have Worksheet_Calculate event code in PERSONAL sheet
which will be firing if the application calculates.

That code is looking for the named ranges which don't exist because the
activesheet is now NEW LIABILITIES.

I am not sure how to overcome that problem other than to turn calculation to
manual when you switch sheets.


Gord

On Sat, 10 Mar 2007 14:10:03 -0800, Brettjg
wrote:

Hey Gord, a little more help if you would. The workbook has three sheets, and
only the sheet 'PERSONAL' has sheetcode. All the locking/unlocking etc works
in that sheet, and I then unprotect it to break links and a few other things
(being careful to unprotect it before each break etc). The I activate the
sheet 'NEW LIABILITIES' and execute the following code:

'COPY "LIABILITIES" FORMULAS
Sheets("NEW LIABILITIES").Select
Sheets("NEW LIABILITIES").Activate (this is probably unnecessary)
Rows("60:238").Delete (this works and happens)
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollRow = 10
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("J21").Select

Now it does all of this except when it gets to protecting the sheet, when it
trips over because of the code in the 'PERSONAL' sheet re locking cells in
'PERSONAL'. I don't understand why the code in 'PERSONAL' should have any
effect on what I do in 'NEW LIABILITIES'

This is the sheetcode for 'PERSONAL':
Private Sub Worksheet_Calculate()
ActiveSheet.UNPROTECT

If Range("add.years.1").Value = 3 Then
ActiveSheet.Range("previous.address.1").Locked = True
Else
'< 3 processing
ActiveSheet.Range("previous.address.1").Locked = False
End If

If Range("yrs.position.1").Value = 3 Then
ActiveSheet.Range("previous.job.1").Locked = True
Else
'< 3 processing
ActiveSheet.Range("previous.job.1").Locked = False
End If
ActiveSheet.Protect
End Sub

It trips over when trying to protect 'LIABILITIES' and debugs to the line
ActiveSheet.Range("previous.address.1").Locked = False
from the sheetcode in 'PERSONAL'.

EH?
Thanks for your help, Brett