VBA for each sheet in book

M

Mark1

I need a line of code that will activate the next sheet in
my workbook without naming the worksheets. For cells it's
easy - you just use the offset method. What about for
actual worksheets?
 
C

Chip Pearson

Mark,

You can use the Next property to return the next worksheet.

If Not ActiveSheet.Next Is Nothing Then
ActiveSheet.Next.Activate
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

Bob Phillips

Mark,

Worksheets are just another collection, so can be referenced via their index
property.

Worksheets.Item(1)
or
Worksheets(1)

etc.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Guest

How does this work? I used it and my code ran!! First
try I just used ActiveSheet.Next.Activate at the bottom of
my code and it didn't work (got an error message). When I
put the whole procedure in, it worked!!
 
C

Chip Pearson

If the ActiveSheet is the last worksheet in the workbook,
ActiveSheet.Next.Activate will fail. The code I post first checks
whether ActiveSheet.Next Is Nothing. If this is false, the Next
sheet is activated. If this is false (the sheet is the last one
in the workbook), no action is taken.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top