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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com