Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VBA select sll sheets based on Criteria on each sheet.
I know how to select all sheets but need to be able to filter which sheets I
want selected. Can I select a sheet in a workbook based on criteria in a specific cell? This is the code I used to select all sheets. Dim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets arySheets(i) = sh.Name i = i + 1 Next sh Sheets(arySheets).Select |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VBA select sll sheets based on Criteria on each sheet.
Try this...
Dim arySheets() as string Dim sh as worksheet Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets if sh.range("A1").value = "Tada" then arySheets(i) = sh.Name i = i + 1 end if Next sh ReDim preserve arySheets(i-1) Sheets(arySheets).Select -- HTH... Jim Thomlinson "AirgasRob" wrote: I know how to select all sheets but need to be able to filter which sheets I want selected. Can I select a sheet in a workbook based on criteria in a specific cell? This is the code I used to select all sheets. Dim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets arySheets(i) = sh.Name i = i + 1 Next sh Sheets(arySheets).Select |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VBA select sll sheets based on Criteria on each sheet.
Jim has given you what looks like a solution to your question... I just
wanted to point out that if you want to select all the worksheets in the active workbook, you can use this single line of code to do that... ActiveWorkbook.Worksheets.Select -- Rick (MVP - Excel) "AirgasRob" wrote in message ... I know how to select all sheets but need to be able to filter which sheets I want selected. Can I select a sheet in a workbook based on criteria in a specific cell? This is the code I used to select all sheets. Dim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets arySheets(i) = sh.Name i = i + 1 Next sh Sheets(arySheets).Select |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VBA select sll sheets based on Criteria on each sheet.
Hi Jim thank you for the response. I ran the code this morning and got a code
break at ReDim Preserve arySheets(i - 1) "Jim Thomlinson" wrote: Try this... Dim arySheets() as string Dim sh as worksheet Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets if sh.range("A1").value = "Tada" then arySheets(i) = sh.Name i = i + 1 end if Next sh ReDim preserve arySheets(i-1) Sheets(arySheets).Select -- HTH... Jim Thomlinson "AirgasRob" wrote: I know how to select all sheets but need to be able to filter which sheets I want selected. Can I select a sheet in a workbook based on criteria in a specific cell? This is the code I used to select all sheets. Dim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets arySheets(i) = sh.Name i = i + 1 Next sh Sheets(arySheets).Select |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VBA select sll sheets based on Criteria on each sheet.
Never mind I figured it out, the criteria has to be exact for example; Yes
must be Yes not YES. "Rick Rothstein" wrote: Jim has given you what looks like a solution to your question... I just wanted to point out that if you want to select all the worksheets in the active workbook, you can use this single line of code to do that... ActiveWorkbook.Worksheets.Select -- Rick (MVP - Excel) "AirgasRob" wrote in message ... I know how to select all sheets but need to be able to filter which sheets I want selected. Can I select a sheet in a workbook based on criteria in a specific cell? This is the code I used to select all sheets. Dim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets arySheets(i) = sh.Name i = i + 1 Next sh Sheets(arySheets).Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move data to new sheet - rename sheet based on criteria ? | Excel Discussion (Misc queries) | |||
Select criteria based on date | Excel Discussion (Misc queries) | |||
Select rows based on criteria | Excel Worksheet Functions | |||
data validation list should have opt. to select based on criteria | Excel Worksheet Functions | |||
Can I select a worksheet based upon a cell criteria?(for printing) | Excel Worksheet Functions |