If WSh.Name < "Admin" and < "Server * Projection Seed Values"

First of all, this line lacks 'WSh.Name' before '< "Server *... '
Obviously this test will not perform a LIKE compare.
To solve your problem, you can do so:

If WSh.Name < "Admin" and Left(WShName, 7) < "Server " and Right(WShName,
23) < " Projection Seed Values" Then

Be attention: "Server " ends with a space, " Projection Seed Values" begins
with a space.

Hi

I need to perform arbitary actions on all sheets in my workbook except for a
sheet with the name "Admin" and any sheets with the following naming
convention "Server * Projection Seed Values". Am at my wits end with this
one. Can anyone help. See my code below;

Dim qt As QueryTable
Dim WSh As Worksheet

For Each WSh In ThisWorkbook.Worksheets
If WSh.Name < "Admin" and < "Server * Projection Seed Values"
Then '<------ This is the problem area
'Perform action here
End If
Next WSh

End Sub

Many Thanks - Grant

If WSh.Name < "Admin" and Left(WShName, 7) < "Server " and
Right(WShName,
23) < " Projection Seed Values" Then


ahem... I wrote "WShName" instead of "WSh.Name"

Hi Grant,

You need to add WSh.name to the second part of the test as well.

If WSh.Name < "Admin" and WSh.Name < "Server * Projection Seed
Values" Then

Cheers
Andy

Grant Reid wrote:

Hi

I need to perform arbitary actions on all sheets in my workbook except for a
sheet with the name "Admin" and any sheets with the following naming
convention "Server * Projection Seed Values". Am at my wits end with this
one. Can anyone help. See my code below;

Dim qt As QueryTable
Dim WSh As Worksheet

For Each WSh In ThisWorkbook.Worksheets
If WSh.Name < "Admin" and < "Server * Projection Seed Values"
Then '<------ This is the problem area
'Perform action here
End If
Next WSh

End Sub

Many Thanks - Grant



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

If WSh.Name < "Admin" and (Left(WShName, 7) < "Server " OR Right(WShName,
23) < " Projection Seed Values") Then

7 and 23 are lengths of "Server " and " Projection Seed Values"

Hi

Many thanks to those who reponded , but...... I'm still not getting the
expected result. I'm trying to refresh all query tables in the workbook,
except for a worksheet named "Admin" and all worksheets with a naming
convention of "Server 1 Database Space Summary", "Server 2 Database Space
Summary", "Server 3 Database Space Summary" etc etc. I've tried variations
of the responses posted thus far but without success. Perhaps I'm missing
the obvious or hitting a late afternoon blank. I've re-posted my code below
hoping that someone will highlight the problem for me. Many thanks in
advance

Sub RefreshExisting()

Dim qt As QueryTable
Dim WSh As Worksheet
Dim strQueryName As String

For Each WSh In ThisWorkbook.Worksheets
For Each qt In WSh.QueryTables
strQueryName = WSh.Range("AM2")
If WSh.Name < "Admin" And WSh.Name < "Server * Projection Seed
Values" Then
If InStr(qt.Name, strQueryName) Then
qt.Refresh BackgroundQuery:=False
End If
End If
Next qt
Next WSh
End Sub

Kind Regards - Grant

If WSh.Name < "Admin" and _
Not WSh.Name like "Server * Projection Seed Values" Then

--

HTH

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

"Grant Reid" wrote in message
...
Hi

I need to perform arbitary actions on all sheets in my workbook except for
a
sheet with the name "Admin" and any sheets with the following naming
convention "Server * Projection Seed Values". Am at my wits end with this
one. Can anyone help. See my code below;

Dim qt As QueryTable
Dim WSh As Worksheet

For Each WSh In ThisWorkbook.Worksheets
If WSh.Name < "Admin" and < "Server * Projection Seed Values"
Then '<------ This is the problem area
'Perform action here
End If
Next WSh

End Sub

Many Thanks - Grant

Does it works? Which solution have you used?

Hi

Many thanks to all who responded.

Kind Regards - Grant

Hi Francesco

I tried all the suggestions and the only ones that did the trick for me were
the solutions posted by you and Bob. Very elegant, both of them.

I do not know why the solution posted by Andy did'nt do it for me. Perhaps
it was late afternoon brain-fog ;-)

Once again, many thanks to you all

Kind Regards - Grant

and I had thought my little gem had got lost in all the fog<vbg

--

HTH

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

"Grant Reid" wrote in message
...
Hi Francesco

I tried all the suggestions and the only ones that did the trick for me
were
the solutions posted by you and Bob. Very elegant, both of them.

I do not know why the solution posted by Andy did'nt do it for me. Perhaps
it was late afternoon brain-fog ;-)

Once again, many thanks to you all

Kind Regards - Grant

I left out the LIKE operator.

Grant Reid wrote:

Hi Francesco

I tried all the suggestions and the only ones that did the trick for me were
the solutions posted by you and Bob. Very elegant, both of them.

I do not know why the solution posted by Andy did'nt do it for me. Perhaps
it was late afternoon brain-fog ;-)

Once again, many thanks to you all

Kind Regards - Grant



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info