Switching to different worksheets during a macro

  • Thread starter Thread starter Michelle72
  • Start date Start date
M

Michelle72

I have a workbook with 10 worksheets in it. The first sheet is a data
entry page and upon filling in all necessary cells, I click on a button
for a macro I have created that runs a series of macros on all sheets.
This macro consists of several other macros...for example, it selects
the next sheet and performs a macro then selects the next sheet and
performs a macro and so on until it has run each macro on the 9
sheets(one on each sheet). Currently, in the large macro on the 1st
sheet it selects the next sheet as follows:

Sheets("name of sheet").Select

Is there anyway to tell it to select the next worksheet without having
to actually have the name of the sheet in there? In the future, I will
be adding and deleting worksheets as my customer base changes and it
would be great to have a "generic select the next sheet until there
aren't anymore code" and it would allow me to not have to edit my
macro(s) everytime that happens

Any help would be greatly appreciated!

Thanks,
Michelle :)
 
Hi Michelle,

Yes, the Next and Previous methods of the Sheet object will return a
reference to the next and previous sheets, respectively. So, to select the
next sheet, you could do this:

ActiveSheet.Next.Select

Keep in mind that this will throw an error if the ActiveSheet is the last
sheet in the workbook. Another option would be to use a For Each ... Next
loop:

Dim ws As Worksheet

For Each ws In Worksheets
MsgBox ws.Name
Next ws

That said, you should really try to avoid selecting sheets/ranges if
possible when automating tasks in Excel. Selecting is seldom necessary;
additionally, it slows down execution and it messes with the users' current
selection/view. As an example, if you are copying/pasting a range of cells
from Sheet1 to Sheet2, you could do it without selecting anything:

Sheet1.Range("A1:B10").Copy Destination:=Sheet2.Range("A1")
 
Each worksheet in your workbook belong to a worksheets collection. You can
step through each member (worksheet) of a collection without reference to
the name. See collections in the help file. Incidently, many other
collections are used by Excel as well. Having a good understanding of this
will be very beneficial.

Also, it is 'generally' better coding practice to use the select and
activate methods, judiciously. You can always create references to
workbooks, worksheets, ranges, etc., without having to select/activate them.

Bill Barclift
 
Back
Top