| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking visible sheets.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
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 http://www.solutionsvba.com "Scott" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
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 wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
checking visible sheets.
That's Fantastic,
Thanks Very Much Tom, Best regards Scott "Tom Ogilvy" wrote in message ... 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 wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checking numbers across various sheets | Excel Worksheet Functions | |||
| Checking a number of sheets | Excel Worksheet Functions | |||
| Checking to see how many workbooks are visible | Excel Discussion (Misc queries) | |||
| Checking if sheet is visible | Excel Discussion (Misc queries) | |||
| checking multiple sheets. | Excel Programming | |||
Linear Mode
