Global Workbook/sheet referance

  • I have a situation where I reference a Profile sheet in a workbook that remains open.

    I'd like to create a reference to that sheet that I can use throughout the code module. For example, instead of

    SSOD.Range("Author").Value = Workbooks("FEC_Mstr.xls").Worksheets("Profile").Range("User").Value

    I'd like to use something like

    SSOD.Range("Author").Value = Profile.Range("User").Value

    I'd rather do this with some kind of Function (or something else) and avoid the Global Variable thing, however I'm not sure how to code it.

    Any help would be appreciated.



  • Re: Global Workbook/sheet referance


    Can't you just set a reference to it like this?

    Set wsProfile = Workbooks("FEC_Mstr.xls").Worksheets("Profile")

    This could be done at the start of the code and used throughout.


  • Re: Global Workbook/sheet referance

    Yes, I could but I'm saying that I'd like to use this reference in multiple sub routines and would rather not have to do the Set each time. In fact in many cases the referenced workbook just shows up once in the sub. Therefore it wouldn't save much.

    Any other idea's or am I not understanding your intent?

    Thank you Norie for the quick response.


  • Re: Global Workbook/sheet referance

    Seems to have fallen in the list (bounce to top).

    Gotta feeling it's pretty simple just drawing a blank on this one.

    Anyone wanta to take a stab???



  • Re: Global Workbook/sheet referance

    You could set a reference to the project workbook.

    While in VBA Editor...... Activate your workbook.

    Tools > References

    Look for your Project name (In fact you should really rename your reference workbook other wise you will get name errors)
    Click on the project name. (just like setting a reference to any other obj library file eg Word etc)

    Now just refrence the name of the project eg


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!