With my first reply I meant that the declaration of the variables would
still occur at the module level it is only the setting of the value
that would occur in the the sub rotine.

Option Explicit

Public PSPDI_WS As Worksheet
Public PSPWO_WS As Worksheet
Public etc, etc...


sub SetPublicVar

Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1")
Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2")
Set etc, etc...

end sub

sub MainMacro
dim variables

call SetPublicVar

more code here

end sub





Chip

I am under the impression that all module and global variables loose
their settings whenever all macro's has finished running which is what
would happen in Mikee-hime's case once he exits the the Auto_Open
routine

If as you have hinted at that my understanding of this is wrong can you
please enlighten me & Mike further


---
Message posted from http://www.ExcelForum.com/