finding order of worksheets 2

[Luis939]

ive been trying to loo all the properties and members of the worksheets item, n i cant seem to find a way to easily determine what number sheet is my particular worksheet in question i just need to know if activesheet is sheets(1) or sheets(2) or whatever nth sheet it is, can anyone help, thanks!!!!!

 

[Zathras]

Play around with this:
[blue]

Sub test()
  MsgBox ActiveSheetPosition
End Sub

Private Function ActiveSheetPosition() As Integer
Dim i As Integer
  For i = 1 To Sheets.Count
    If Sheets(i).Name = ActiveSheet.Name Then
      ActiveSheetPosition = i
      Exit For
    End If
  Next i
End Function

[/color]

 

[Luis939]

pretty smart n logical idea, thanks zathras!!!

 

[RobBroekhuis]

What am I missing? How about Activesheet.index?


Rob

 

[Luis939]

Nice!

 

[Luis939]

But, how about this
if i choose something like

dim wkst as worksheet

for each wkst in activeworkbook.worksheets

could i choose the next wkst without using the NEXT statement

perhaps something like wkst + 1 or + 2 to indicate 2 sheets after? or should i just stick with something like

for i=1 to activeworkbook.sheets.count
thanks

 

[RobBroekhuis]

If you want to activate (I assume that's what you mean by "choose&quot the worksheet two tabs away, you could say:

sheets(activesheet.index+2).activate

Does that help?

Rob

 

[Luis939]

well i think my logic was wrong
i wanted to run a for each loop until a certain worksheet and then run a 2nd for each loop from the next worksheet on, but i dont think i can use

for each wkst+1 in activeworkbook.sheets

i guess i would have to do that through

for i= wkst.index + 1 to sheets.count

 

[RobBroekhuis]

Yes, that looks about right.

Rob