Hi

I have named 3 sheets. I want to do a "for each" statement
but i am missing something. see below

For Each s In ThisWorkbook.Worksheets(wsPLimagine, wsPLos,
wsPLcurrency)

any ideas

thanks

Kevin,

I am not sure what you are trying to do, access the 3 sheets, or name them,
but the for Next loop should look like

For Each s In ThisWorkbook.Worksheets
MsgBox s.Name 'just as an example
Next s

You cannot do a For next on an array of sheet names as you seem to be trying
to do, but only on the whole collection.

You could test within the loop, like so

For Each s In ThisWorkbook.Worksheets
If s.Name = wsPLimagine Or s.Name = wsPLos Or s.Name = wsPLcurrency
Then
' do your stuff
End If
Next s

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kevin" wrote in message
...
Hi

I have named 3 sheets. I want to do a "for each" statement
but i am missing something. see below

For Each s In ThisWorkbook.Worksheets(wsPLimagine, wsPLos,
wsPLcurrency)

any ideas

thanks

Bob/Kevin

This works for me.......

Public Sub lookat()
For Each s In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
MsgBox s.Name 'just as an example
Next s
End Sub

Gord Dibben XL2002

On Thu, 27 Nov 2003 21:34:15 -0000, "Bob Phillips"
wrote:

Kevin,

I am not sure what you are trying to do, access the 3 sheets, or name them,
but the for Next loop should look like

For Each s In ThisWorkbook.Worksheets
MsgBox s.Name 'just as an example
Next s

You cannot do a For next on an array of sheet names as you seem to be trying
to do, but only on the whole collection.

You could test within the loop, like so

For Each s In ThisWorkbook.Worksheets
If s.Name = wsPLimagine Or s.Name = wsPLos Or s.Name = wsPLcurrency
Then
' do your stuff
End If
Next s

Thanks Bob - thats works fine - i was trying to access the
3 sheets
cheers
-----Original Message-----
Kevin,

I am not sure what you are trying to do, access the 3
sheets, or name them,
but the for Next loop should look like

For Each s In ThisWorkbook.Worksheets
MsgBox s.Name 'just as an example
Next s

You cannot do a For next on an array of sheet names as
you seem to be trying
to do, but only on the whole collection.

You could test within the loop, like so

For Each s In ThisWorkbook.Worksheets
If s.Name = wsPLimagine Or s.Name = wsPLos Or
s.Name = wsPLcurrency
Then
' do your stuff
End If
Next s

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kevin" wrote in
message
...
Hi

I have named 3 sheets. I want to do a "for each"
statement
but i am missing something. see below

For Each s In ThisWorkbook.Worksheets(wsPLimagine,
wsPLos,
wsPLcurrency)

any ideas

thanks


.

Kevin,

Have you also seen Gord's response, it is more aligned to your original
idea?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"KEVIN" wrote in message
...
Thanks Bob - thats works fine - i was trying to access the
3 sheets
cheers
-----Original Message-----
Kevin,

I am not sure what you are trying to do, access the 3
sheets, or name them,
but the for Next loop should look like

For Each s In ThisWorkbook.Worksheets
MsgBox s.Name 'just as an example
Next s

You cannot do a For next on an array of sheet names as
you seem to be trying
to do, but only on the whole collection.

You could test within the loop, like so

For Each s In ThisWorkbook.Worksheets
If s.Name = wsPLimagine Or s.Name = wsPLos Or
s.Name = wsPLcurrency
Then
' do your stuff
End If
Next s

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kevin" wrote in
message
...
Hi

I have named 3 sheets. I want to do a "for each"
statement
but i am missing something. see below

For Each s In ThisWorkbook.Worksheets(wsPLimagine,
wsPLos,
wsPLcurrency)

any ideas

thanks


.