Hi, I am looking for a piece of code that can pick up all tabs/sheets after
the tab where the button is located to run the code, regardless of name and
number of tabs/sheets. is this possible?
Reason being: The first 2 tabs will be unchanged, but the workbook can have
different amounts of tabs with different names, and I want to select all the
tabs so that I can format all at the same time (using code)
Many thanks
John

The following code will put the names of the worksheet to the right of
Sheet3 in the array named Arr and then select those sheets.

Dim N As Long
Dim Arr() As String
With ThisWorkbook.Worksheets
ReDim Arr(.Item("Sheet3").Index + 1 To .Count)
For N = .Item("Sheet3").Index + 1 To .Count
Arr(N) = .Item(N).Name
Next N
End With
ThisWorkbook.Worksheets(Arr).Select

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 17 Oct 2008 10:25:01 -0700, JohnUK
wrote:

Hi, I am looking for a piece of code that can pick up all tabs/sheets after
the tab where the button is located to run the code, regardless of name and
number of tabs/sheets. is this possible?
Reason being: The first 2 tabs will be unchanged, but the workbook can have
different amounts of tabs with different names, and I want to select all the
tabs so that I can format all at the same time (using code)
Many thanks
John

you can see if this works for you:

Sub test()
Dim i As Long
For i = ActiveSheet.Index + 1 To Worksheets.Count
Worksheets(i).Select False
Next
End Sub

--


Gary

"JohnUK" wrote in message
...
Hi, I am looking for a piece of code that can pick up all tabs/sheets
after
the tab where the button is located to run the code, regardless of name
and
number of tabs/sheets. is this possible?
Reason being: The first 2 tabs will be unchanged, but the workbook can
have
different amounts of tabs with different names, and I want to select all
the
tabs so that I can format all at the same time (using code)
Many thanks
John

Thanks guys, they both worked as equally as well
Regards
John


"Gary Keramidas" wrote:

you can see if this works for you:

Sub test()
Dim i As Long
For i = ActiveSheet.Index + 1 To Worksheets.Count
Worksheets(i).Select False
Next
End Sub

--


Gary

"JohnUK" wrote in message
...
Hi, I am looking for a piece of code that can pick up all tabs/sheets
after
the tab where the button is located to run the code, regardless of name
and
number of tabs/sheets. is this possible?
Reason being: The first 2 tabs will be unchanged, but the workbook can
have
different amounts of tabs with different names, and I want to select all
the
tabs so that I can format all at the same time (using code)
Many thanks
John