checking visible sheets.

[Scott]

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]

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

 

[Tom Ogilvy]

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

 

[Scott]

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





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

 

[Scott]

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


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





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

 

[Tom Ogilvy]

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

 

[Scott]

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






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