Runtime Error with Excel VB Sub

R

Rex

I set up a 4-sheet Excel workbook and I have a VB subroutine that
manipulates ranges across all the pages. The subroutine works ok if
called from sheets 2, 3, or 4 but not from sheet 1.

I set up a button on sheet1 that has this action:
----
TestLog.xls - Sheet1

Private Sub CommandButton1_Click()
Call ActiveWorkbook.CalcUnique
End Sub
----

CalcUnique is my subroutine that begins this way:
----
TestLog.xls - ThisWorkbook

Sub CalcUnique()
Dim Band As Integer
' other Dims deleted

[UCalls3].ClearContents 'Clear Unique list for all bands
----

When called from the button on sheet1 or from the toolbar macros, while
sheet1 is active I get this error:
----
Run-time error 1004
ClearContents method of Range class failed

The error points to the line [UCalls].ClearContents in the subroutine.
UCalls3 is a named range:
----
Range name definition:
UCalls3 ='Unique Wrkspace'!$B$5:$F$205
('Unique Wrkspace' is sheet4)
----

If I use the Macro toolbar to call up the same subroutine CalcUnique()
while Sheet2, 3, or 4 is active, the Sub seems to work fine with no
errors. The Macro toolbar while Sheet1 is active, fails the same way.

I haven't done much of this (Excel or VB), but I can't understand the
problem or how to resolve it.

Can someone help me?

thanks,
-Rex
 
Top