Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Next Sheet In A Macro

    A friend is creating a macro that performs certain tasks on each worksheet tab in the workbook. The tab name will be 5-7 digit numbers randomly assigned by the user. The macro needs to do is something like:
    Sheet1.Activate
    Code..........
    Sheet2.Activate
    Etc.

    If tab1 name is (for instance) 12345 and tab 2 is 4567801, will referring to them as sheet1, sheet2 work? Ideally, the macro should start with the first tab, perfom the code, select NEXT TAB, perform the code, etc. until there are no more tabs left.

    Can someone put us on the right track for:

    1. Selecting sheets without calling them by the name the user assigns to it AND

    2. Looping the macro until there are no more tabs?

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Next Sheet In A Macro

    There are many ways to do what you are asking, depending on your exact requirements.

    First, even though the sheets have been renamed, the original object still exists. Therefore, if you rename Sheet1 to 12345, the Sheet1 object still exists and can be used to refer to the sheet.

    Second, you can use the Worksheets collection with a numeric index to refer to the worksheets in the workbook. WorkSheets(1) refers to the first sheet in the workbook.

    You can use the For Each construct to reference all of the sheets in the workbook like this:

    <pre>Dim oNextSheet As Worksheet
    For Each oNextSheet In Worksheets
    MsgBox oNextSheet.Name
    Next oNextSheet
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Next Sheet In A Macro

    The NextSheet suggestion will work fine. I am curious though, why do you refer to it as oNextSheet ?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Next Sheet In A Macro

    A sheet is an object, so it's best to use a variable naming convention on your variables. ovariablename would let others who read your macro know it's an object. If you were to do an integer, ivariablename.

    Examples: oNextSheet, iCounter ...

    See the VB/VBA forum for variable naming conventions.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Next Sheet In A Macro

    NextSheet is just a name I made up and can be anything you want it to be. The "o" is part of my naming convention and does two things. First, it keeps me from getting in trouble with reseved words when I name variables (ie Next is an invalid variable name but oNext is OK). Second, it tells me what kind of variable it is. "o" means it is an Object variable. "i" would designate Integer, "l" Long, "str" String, "d" Double, etc.
    Legare Coleman

Posting Permissions

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