Refer to Sheet by Secret Name

    When I look in VBA under the Microsoft Excel Objects at the sheet names in my workbook I see two names for the same sheet like so:


    sOrdersFB(Orders From Bob)


    Can someone tell me how to select using the first name sOrdersFB?

    Re: Refer to Sheet by Secret Name


    Welcome to the OzGrid Forum!


    The first name you see is called the code name of the sheet. You can (should) use it in macros. Instead of a line like
    [VBA]Sheets("Orders From Bob").Range("A1")=2[/vba]
    you can use
    [VBA]sOrdersFB.Range("A1")=2[/vba]

    Re: Refer to Sheet by Secret Name


    Perhaps you're not already on that sheet. Try this:

    Code
    sOrdersFB.Activate
sOrdersFB.Range("A1").Select


    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

    Re: Refer to Sheet by Secret Name


    OK. The select works but now I can't paste.


    [code] sOrdersFB.Cells.Clear
    Workbooks(sBobName).Sheets("Orders").Cells.Copy
    Workbooks(sTrafficFileName).Activate
    sOrdersFB.Range("a1").Paste
    [\code]

    Re: Refer to Sheet by Secret Name


    OK. The select works but now I can't paste.


    Code
    sOrdersFB.Cells.Clear
Workbooks(sBobName).Sheets("Orders").Cells.Copy
Workbooks(sTrafficFileName).Activate
sOrdersFB.Range("a1").Paste


    Thanks again,
    Jim

    Re: Refer to Sheet by Secret Name


    Your syntax is slightly off:

    Code
    sOrdersFB.Paste ("A1")


    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

    Re: Refer to Sheet by Secret Name


    It still hangs at the paste command.


    Code
    Workbooks(sTrafficFileName).Activate
    sOrdersFB.Cells.Clear
    Workbooks(sBobName).Sheets("Orders").Cells.Copy
    Workbooks(sTrafficFileName).Activate
    sOrdersFB.Paste ("A1")


    Jim

    Re: Refer to Sheet by Secret Name


    Indeed. It seems that VBA doesn't want to follow what's in the help files. :confused:


    How about this:


    Code
    Workbooks(sTrafficFileName).Activate 
sOrdersFB.Cells.Clear 
Workbooks(sBobName).Sheets("Orders").Cells.Copy sOrdersFB.Range("A1")


    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

    Re: Refer to Sheet by Secret Name


    Beautiful. That works. I don't want to push my luck but, is there a way to combine the activate and clear lines?


    Thanks a million,
    Jim

    Re: Refer to Sheet by Secret Name


    sOrdersFB is the code name of the sheet in question.
    sTrafficFileName is the workbook sOrdersFB is in.


    This works:

    Code
    Workbooks(sTrafficFileName).Activate
sOrdersFB.Cells.Clear


    This doesn't:

    Code
    Workbooks(sTrafficFileName).Sheets(sOrdersFB).Cells.Clear


    Any ideas?


    Thanks,
    Jim

    Re: Refer to Sheet by Secret Name


    Jim


    You're really mixing things up here.:)


    In the first piece of code you are using the codename, then you are trying to refer to the worksheet using Sheets(sOrdersFB).


    In the 2nd piece of code VBA sees sOrdersFB as a variable and since you don't assign it a value anywhere it's empty/zero.

    Boo!:yikes:

    Re: Refer to Sheet by Secret Name


    Ok. How do I know that the code will know which workbook to find sOrdersFB in when I'm jumping back and forth between workbooks in other parts of the code. I thought I would have to activate the workbook to be sure I don't erase another sheet.


    Thoughts?


    Jim

    Re: Refer to Sheet by Secret Name


    Jim


    Like Derk says you don't need to activate/select worksheets/workbooks/ranges/etc to work with them.


    What are you actually trying to do?

    Boo!:yikes:

    Re: Refer to Sheet by Secret Name


    I'm trying to combine the following two statements.


    Code
    Workbooks(sTrafficFileName).Activate 
sOrdersFB.Cells.Clear


    Because, those statements come between parts of code that acts on other workbooks. I thought if I just use the second line for clearing while the focus is on another workbook, it would crash. No?


    Jim

    Re: Refer to Sheet by Secret Name


    Here's the code:


    Code
    Workbooks(sTrafficFileName).Activate
    sOrdersFB.Cells.Clear
    Workbooks(sBobName).Sheets("Orders").Cells.Copy sOrdersFB.Range("A1")
    sOrdersFB.Cells.Copy
    sOrdersFB.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    sOrdersFB.Range("A4").Select


    It works but, when it is called from another module, do I need to activate before I clear sOrdersFB?


    Also, the last line doesn't seem to work if the macro runs while another sheet is selected.


    Thanks,
    Jim

    Re: Refer to Sheet by Secret Name


    Jim


    Is that all the code?


    I don't see where sTrafficFileName is being defined.


    It might also help if you explained what you are trying to achieve.

    Boo!:yikes:

Participate now!

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

Register Yourself Login