VBA for each sheet in book

  • Thread starter Thread starter Mark1
  • Start date Start date
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?
 
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
 
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)
 
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!!
 
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
 
Back
Top