john.mctigue
12/14/2006 12:25:00 PM
Excel 2002 SP-2
I have a series of workbooks that contain identical code. This
includes formatting of a worksheet area which is triggered on opening
the workbook:
Private Sub Workbook_Open()
'---------------------'
'Name the 'Base' range'
'---------------------'
ThisWorkbook.Names.Add Name:="Base", _
RefersTo:="=Sheet1!$A$21", Visible:=True
Call SetupTemplateArea
.....
Sub SetupTemplateArea()
Dim mysheet As Worksheet
Set mysheet = ThisWorkbook.Worksheets("Sheet1")
'Define and format the template work area
mysheet.Range(Range("Base").Offset(0, 0), Range("Base").Offset(5,
8)).Name = "TEMPLATE_AREA"
If I open more than one workbook from the same Open dialog box then the
code fails at the line above for all workbooks, except for the last
which opens normally after the code in each of the other workbooks is
reset. The error is Run-time error '1004': Method 'Range' of object
'_Worksheet' failed.
The workbooks behave normally when opened individually.
Any help would be appreciated.
Kind regards,
John