Before you can activate a worksheet you have to activate the workbook...
Give this a try...

Public WSS As Worksheet, WSC As Worksheet, WSD As Worksheet, WSO As Worksheet
Public WBS As Workbook, WBD As Workbook

Public Sub Auto_Open()
ChDir ("D:\My Documents\Excel\Calc")
Workbooks.Open Filename:="WTNDatabase.xls"

Set WBS = Workbooks("WTNSystem")
Set WBD = Workbooks("WTNDatabase")

Set WSS = WBS.Worksheets("System")
Set WSC = WBS.Worksheets("Calculation")
Set WSD = WBD.Worksheets("Database")
Set WSO = WBD.Worksheets("Offers")

WBS.Activate 'Activate Book
WSS.Activate
MsgBox "We are Here"
WBD.Activate 'Activate Book
WSD.Activate
MsgBox "And now we are here..."
End Sub
--
HTH...

Jim Thomlinson


"Mats Samson" wrote:

Hello,
Im creating references/variables to other worksheets in other workbooks
but they dont work when returning to the original workbook sheet.
€œRun time error 91: Object variable or With block variable not set€�
The two workbooks are loaded at one time and in the first/€�main€�
(WTNSystem.xls)
there is a module containing:
Public WSS As Worksheet, WSC As Worksheet, WSD As Worksheet, WSO As Worksheet
Public Sub Auto_Open()
ChDir ("D:\My Documents\Excel\Calc")
Workbooks.Open Filename:="WTNDatabase.xls"
Set WSS = Workbooks("WTNSystem").Worksheets("System")
Set WSC = Workbooks("WTNSystem").Worksheets("Calculation")
Set WSD = Workbooks("WTNDatabase").Worksheets("Database")
Set WSO = Workbooks("WTNDatabase").Worksheets("Offers")
WSS.Activate
End Sub

The second workbook is opened and everything works fine as long as I stay
within the €œmain€� workbook and I can switch to the WTNDatabase.xls workbook
but returning to WTNsystem.xls with the code: WSC.Activate triggers the error.

I even put the same code as above in the WTNDatabase.xls , but it doesnt
help!!!
I cannot switch back to WTNSystem.xls with this €œabbreviation€�.

Why?