checking visible sheets.

Scott

unread,

Aug 29, 2003, 12:01:11 PM8/29/03

to

Hi,

Can someone please help,

I'm trying to create this code which does the following:-
check visible sheets only, not hidden sheets.

check the first sheet
if it is equal to a list of names in an array then goto the next sheet
if it is not equal to a any of the names in an array then do some code
(which I have created)
then goto the next sheet and do the same again

Can someone please help,
Thanks
Best regards,
Scott

Jean-Paul Viel

unread,

Aug 29, 2003, 12:49:26 PM8/29/03

to

Hi,

Use this macro :

Sub ScanSheet()

Dim ws As Worksheet

For Each ws In Worksheets

If ws.Visible Then

' your code

MsgBox ws.Name

End If

Next ws

End Sub

--
JP
j...@solutionsvba.com
http://www.solutionsvba.com

"Scott" <sty...@hotmail.com> wrote in message
news:OGVUHbkb...@tk2msftngp13.phx.gbl...

Tom Ogilvy

unread,

Aug 29, 2003, 1:09:12 PM8/29/03

to

Dim varr as variant, res as variant
Dim sh as worksheet
varr = Array("Sheet3","Sheet9","Data","AAAA")
for each sh in thisworkbook.worksheets
if sh.Visible = xlSheetVisible then
res = Application.Match(sh.name,varr,0)
if iserror(res) then
' not found in the list, do code
end if
end if
Next

--
Regards,
Tom Ogilvy

"Scott" <sty...@hotmail.com> wrote in message
news:OGVUHbkb...@tk2msftngp13.phx.gbl...

Scott

unread,

Sep 1, 2003, 11:06:54 AM9/1/03

to

Thank very much Tom for your help.

I have a question, how do I make each sheet activate one at a time that's
not in the array.

Dim varr as variant, res as variant
Dim sh as worksheet
varr = Array("Sheet3","Sheet9","Data","AAAA")
for each sh in thisworkbook.worksheets
if sh.Visible = xlSheetVisible then
res = Application.Match(sh.name,varr,0)
if iserror(res) then
' not found in the list, do code

' Activate sheet here, but don't know how.

end if
end if
Next

Hope someone can help,
Thanks
best regards,
Scott

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:e7fPLAlb...@TK2MSFTNGP12.phx.gbl...

Scott

unread,

Sep 1, 2003, 11:20:30 AM9/1/03

to

Also I seem to be having a problem with case sensitive again, it seems to
work in a new workbook, but for some reason it doesn't work for the workbook
I'm working on. Tom you where so kind and sorted this out last time I had a
problem, any chance you're able to sort this one out as well.

Thanks
Best regards,
Scott

"Scott" <sty...@hotmail.com> wrote in message

news:%23AJ6uqJ...@TK2MSFTNGP11.phx.gbl...

Tom Ogilvy

unread,

Sep 1, 2003, 12:06:17 PM9/1/03

to

Dim varr as variant, res as variant
Dim sh as worksheet
varr = Array("Sheet3","Sheet9","Data","AAAA")

for i = lbound(varr) to ubound(varr)
varr(i) = ucase(Application.Trim(varr(i)))
Next

for each sh in thisworkbook.worksheets
if sh.Visible = xlSheetVisible then

res = Application.Match(Application. _
Trim(Ucase(sh.name)),varr,0)

if iserror(res) then
' not found in the list, do code
' Activate sheet here, but don't know how.

Sh.Activate

end if
end if
Next

--
Regards,
Tom Ogilvy

Scott <sty...@hotmail.com> wrote in message

news:#1tXVyJc...@tk2msftngp13.phx.gbl...

Scott

unread,

Sep 1, 2003, 12:21:03 PM9/1/03

to

That's Fantastic,
Thanks Very Much Tom,
Best regards
Scott

"Tom Ogilvy" <twog...@msn.com> wrote in message

news:uRgVaKKc...@TK2MSFTNGP12.phx.gbl...