+ Reply to Thread
Results 1 to 5 of 5

Worksheet tabs in a Macro

  1. #1
    Registered User
    Join Date
    11-08-2005
    Posts
    2

    Worksheet tabs in a Macro

    I have a macro that references the sheet tab name that I have given it. The problem is these Tab names change periodically, And that means I have to run a find and replace function to change my Macro with these tab names with the new names. When I look at the module in the Macro screen they show sheet1=(my Name). But what i need is to reference my macro to the Excel default name sheet1, sheet2, sheet3,...etc. I hope I explained my issue.

    Thanks for any help

  2. #2
    Don Guillett
    Guest

    Re: Worksheet tabs in a Macro

    try sheet7.etc

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "jstoner50" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I have a macro that references the sheet tab name that I have given it.
    > The problem is these Tab names change periodically, And that means I
    > have to run a find and replace function to change my Macro with these
    > tab names with the new names. When I look at the module in the Macro
    > screen they show sheet1=(my Name). But what i need is to reference my
    > macro to the Excel default name sheet1, sheet2, sheet3,...etc. I hope
    > I explained my issue.
    >
    > Thanks for any help
    >
    >
    > --
    > jstoner50
    > ------------------------------------------------------------------------
    > jstoner50's Profile:

    http://www.excelforum.com/member.php...o&userid=28651
    > View this thread: http://www.excelforum.com/showthread...hreadid=483154
    >




  3. #3
    John Michl
    Guest

    Re: Worksheet tabs in a Macro

    Have you considered using the sheet index instead of the name? Sheet1
    is always an index of 1 even though the name changes. I'd probably DIM
    the sheets at the beginning of the code. For instance, assume Sheet1
    was renamed to "Info", Sheet2 to "Data" and Sheet3 "Results". Then....

    Sub SheetNames()
    Dim shInfo, shData, shResults As Worksheet

    Set shInfo = Sheets(1)
    Set shData = Sheets(2)
    Set shResults = Sheets(3)

    i = 1
    For Each sh In Sheets
    MsgBox Sheets(i).Name
    i = i + 1
    Next
    End Sub


    HTH - John
    www.JohnMichl.com


  4. #4
    Registered User
    Join Date
    11-08-2005
    Posts
    2

    Worksheet tabs in a Macro

    The problem is that Excel looks at the name of the tab you have given it. I'm trying to use Excels default name when you first start out Excel creates a sheet1 once you have changed the name Excel does not recognize sheet1 again. I hope this clears up the confusion.

    Jim

  5. #5
    John Michl
    Guest

    Re: Worksheet tabs in a Macro

    Jim, my method uses the index for the sheet which you cannot change.
    Sheet1 always has an index of 1 even when you change the name to
    something other than Sheet1.

    - John


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1