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

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

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

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

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