Calling Next Sheet In A Macro

Topic

New Reply

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?

TIA

Reply | Quote

Replies

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:

Dim oNextSheet As Worksheet
    For Each oNextSheet In Worksheets
        MsgBox oNextSheet.Name
    Next oNextSheet

Reply | Quote

The NextSheet suggestion will work fine. I am curious though, why do you refer to it as oNextSheet ?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote