loop through tabs
 

Hi
Can anyone help me loop through the tabs in a certain position?
I currently use:
for each ws in thisworkbook.worksheets
but the loops all, I only want to loop the tabs named between eg USaa and USzz

Grateful for all help.

loop through tabs
 

I think you would have to test them

For Each ws In Thisworkbook.Worksheets
If Left(ws.Name,2) = "US" Then
'do your stuff
End If
Next ws

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Newbie" wrote in message
...
Hi
Can anyone help me loop through the tabs in a certain position?
I currently use:
for each ws in thisworkbook.worksheets
but the loops all, I only want to loop the tabs named between eg USaa and
USzz

Grateful for all help.

loop through tabs
 

Newbie

If you are looking for something like

For each wks in ThisWorkbook.Worksheets("US*")

it doesn't exist

You could set up an array of the worksheet names and iterate through that
array or, for the speed that Excel will check the sheet tab names just
exclude the code running using and If...Then...End If. That below will
simply move to check the next sheet if the first two characters are not US

For Each wks in ThisWorkbook.Worksheets
If UCase(Left(Wks.Name,2))="US" Then
'Do stuff here
End If
Next wks

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Newbie" wrote in message
...
Hi
Can anyone help me loop through the tabs in a certain position?
I currently use:
for each ws in thisworkbook.worksheets
but the loops all, I only want to loop the tabs named between eg USaa and
USzz

Grateful for all help.

loop through tabs
 

Hell Bob, at least I put a UCase in ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Nick Hodge" wrote in message
...
Newbie

If you are looking for something like

For each wks in ThisWorkbook.Worksheets("US*")

it doesn't exist

You could set up an array of the worksheet names and iterate through that
array or, for the speed that Excel will check the sheet tab names just
exclude the code running using and If...Then...End If. That below will
simply move to check the next sheet if the first two characters are not US

For Each wks in ThisWorkbook.Worksheets
If UCase(Left(Wks.Name,2))="US" Then
'Do stuff here
End If
Next wks

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Newbie" wrote in message
...
Hi
Can anyone help me loop through the tabs in a certain position?
I currently use:
for each ws in thisworkbook.worksheets
but the loops all, I only want to loop the tabs named between eg USaa and
USzz

Grateful for all help.

loop through tabs
 

Try something like

Dim N As Long
Dim WS As Worksheet
For N = Worksheets("USaa").Index To Worksheets("USzz").Index
Set WS = Worksheets(N)
''''''''''''''''''''''''''''''''''''''
' Done something with WS
''''''''''''''''''''''''''''''''''''''
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Newbie" wrote in message
...
Hi
Can anyone help me loop through the tabs in a certain position?
I currently use:
for each ws in thisworkbook.worksheets
but the loops all, I only want to loop the tabs named between eg USaa and
USzz

Grateful for all help.