In , Tom Ogilvy
spake thusly:

You allude to the fact that the first sheet in the series will be different
depending on the sheet being checked. In you example you use
Open Orders - P 10 sheet being checked
Open Orders - P 1 first sheet in the series

However, in your code, you don't check sheets that don't start with "Order
Status"

Tom,

I mightily appreciate your tackling this. I didn't have access
to the workbook in question for a couple of days, so I didn't get
back to this when I wanted to. But now I'm looking at it again.
I'm most grateful for the help.

I misspoke, and that caused the confusion with regard to the
sheet names. Sorry. The sheets are called "Order Status - P1"
through "... - P 10". No sheet in the book is called "Open Orders ..."
But the book itself is called "Open Orders - Queries". So that's
why I managed to mix the names up and confuse both you and me in the
process.


so how is the first sheet in the series to be determined
? if the name has 2 numbers on the right, then use the first
digit of the two?
? Would "Order Status" then be replace with "Open Orders"


Aside from the name mix-up, here is my thinking on running through the
sheets. There are the numbered sheets, and there are two other
sheets with WebQueries I'll be updating, and there is the main
sheet, called "MergeSheet", which has no WebQuery and isn't part
of my run.

I put in my current macro:

Debug.Print sh.Name & " completed..."

so I could see and show you what the names are that get recognized
by the For-loop.

Order Status - P 1 completed...
Order Status - P 2 completed...
Order Status - P 3 completed...
Order Status - P 4 completed...
Order Status - P 5 completed...
Order Status - P 6 completed...
Order Status - P 7 completed...
Order Status - P 8 completed...
Order Status - P 9 completed...
Order Status - P 10 completed...
Order Status - New completed...
Order Status - Pending completed...

So that's fine and what I want. I was merely trying not to run the
queries when they aren't necessary. For the sheets with a "page
number" in their sheetname, that would be: if something (actually a
string) that looks like that number appears in cell A1 of the FIRST
sheet, run the current sheet's query; otherwise, don't.

Here is what it says in A1 of sheet1 now:

Page 1 2 3 4 5 6 7 8 9

(What happens is, when I open the workbook I go to that Sheet1
and log in to the web query page manually to initialize it with
username and password. Sheet1's data range properties are set to
run that query automatically every 10 minutes -- which is [barely]
often enough to keep the source web page from closing the login
and requiring a new manual login with password.

(I don't run the other sheets automatically, because (1) it is
disruptive to my work when the queries happen, so I'd rather they
didn't unless I need the updates; and (2) sometimes I need updates
more often than every 10 minutes, but other times I don't need them
for hours at a time.

(So anyway, the query runs, and Sheet1's A1 cell gets populated
with that text which happens to show me how many sheets of
data there are right now.)

Right now I wouldn't need to run the Sheet10 query, but I'd
need all the others. This is what I was asking about initially.
And I do have that part more or less working now.

The current code is this (changed some since I posted it the other
day). I will say more below this:

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then

' if the right two chars of the current sheet's name _
appear on or after char 5 of the 1st sheet's A1 ...

If InStr(5, worksheets("Order Status - P 1").Range("A1"), _
Right(sh.Name, 2)) 0 Then

Debug.Print "yeah" ' this works
End If

sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
End If
Next

Sheet0.Select ' this is "MergeSheet" and doesn't have a query to run
End Sub


Okay, well, 'Debug.Print "yeah"' is obviously just for me
to get myself going. It works, and here is the answer to
your second question, which, again, was:

? if the name has 2 numbers on the right, then use the first
digit of the two?

The info in Sheet1!A1 has whitespace between each listed page
number. If there is a page 10 listed, it looks like so in A1:

Page 1 2 3 4 5 6 7 8 9 10

So if I grab the right two chars of my current sheetname and
look for that in that string, well, if the right two chars are
" 1" we'll find it; and if the right two chars are "10", we'll
also find it in the above example. Earlier pages always run,
so there's never going to be a situation where the info in A1
says "Page 8 9 10" but doesn't also say "Page 1". So
that's why I decided to look here to decide whether to run the
query for each page.

Actually, the A1 message will appear on all the pages in this
subset. But what's in A1 on the later sheets might be old data.
So by looking on Sheet1, I'm sure it's current.

I will study your code and try to implement it as needed.
I'm thinking I ought to go with a Case statement, though.
That's because I want to run

* Order Status - P 1: every time, and use its A1 as described above
* The other "... - P x" sheets provided they appear in that
list.

* Order Status - New: every time (but it doesn't have the A1 info
and it isn't incuded in what's on the Sheet1!A1 list)

* Order Status - Pending: run if the U.S. stock markets are CLOSED;
otherwise, don't bother running it. It, too, is not showin in
Sheet1!A1.

There's one more sheet, called "Order Stat. - Executed - P 1", that
I run only occasionally and hence manually. I abbreviated the word
"Stat." in the sheetname quite on purpose so I could keep it out of
my loop.

Okay, <whew! that was a long explanation, but if you are able
to help further, It would be very cool. Meanwhile, as said, I'll
study what you gave me so far.

Dallman

P.S. Would you or would someone please explain how the "on Error"
thing works?

=================================================
Assume that is the case

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then
sName = Replace(sh.Name,"Order Status","Open Orders")
sName = Left(sName,len(sName)-1)
on Error Resume Next
set sh = worksheets(sName)
on Error goto 0
bskip = False
if not sh is nothing then
if application.Countif(sh.Range("A:A","*" & _
Right(sh.Name,2) & "*") 0 then
bskip = True
end if
end if
' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."
if not bskip then
sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
end if
End If
Next

Take the right "*" off in the countif if column 1 will contain the two digit
number only on the right end of the string.

(I saw your other message with the correction about column to row.)