Making a sheet active How-To ...

[wolfess]

Desired behaviour: Activate a particular sheet in a workbook based on its name.

I've been using the search feature of this forum and found this code snippet.

Sub test()
For Each sht In ActiveWorkbook.Worksheets
"Your code"
Next sht
End Sub

So, to accomplish the above desired behaviour would the below modification to the snippet be correct?

Sub test()
For Each sht In ActiveWorkbook.Worksheets
sht.activate
if activesheet.name = "Sep 05"
Exit
endif
Next sht
End Sub

Also, is there a way to enumerate the sheets without activating them and activate only when the correct sheet is found?

As always, TIA

Steve

 

[xlbo]

no need to enumerate:

Sheets("SheetName").activate

Almost all objects in excel can be used like this - there is no need to enumerate through collections

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

I think what xlbo might have meant to say was, no need to Activate each sheet...

Sub test()
For Each sht In ActiveWorkbook.Worksheets
  if sht.name = "Sep 05" then
     sht.Activate
     Exit sub
  endif
Next sht
End Sub

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Why not simply this ?
ActiveWorkbook.Worksheets("Sep 05").Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wolfess]

Both the Sheets("SheetName").Activate and the
ActiveWorkbook.Worksheets("Sep 05").Activate
methods work.

Direct access via a method is always better than enumerating an object. I have put in a request for the book that was suggested earlier that lays out the Excel Object Model -- that will help a lot. Without knowing the object's methods and properties addressable names or legal aliases it is kind of hard to get things to work.

Thanks a bunch to all contributors, your help is very much appreciated.

Steve

 

[PHV]

that lays out the Excel Object Model
I personally only play with the F2 (object browser) and F1 (VBA help) keys.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

seconded - a quick press of the F2 key in the VBE will give you the object model

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question