PDA

View Full Version : Solved: How to selct sheets starting with certain word



vicks
02-10-2009, 05:00 AM
Hi

I have a simple problem. I have eight sheets, after running the programme they are all selected. I have to do following

1. Deselct all sheets
2. Select four sheets starting with word ABCD
3. Deselect these sheets
4. Select sheets starting with XYZW

Thanks

VIcks

Bob Phillips
02-10-2009, 06:24 AM
Dim aryNames As Variant
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

ReDim aryNames(1 To .Worksheets.Count)

For Each sh In .Worksheets

If sh.Name Like "ABC*" Then

i = i + 1
aryNames(i) = sh.Name
End If
Next sh
If i > 0 Then

ReDim Preserve aryNames(1 To i)
Worksheets(aryNames).Select
End If

i = 0
For Each sh In .Worksheets

If sh.Name Like "XYZ*" Then

i = i + 1
aryNames(i) = sh.Name
End If
Next sh
If i > 0 Then

ReDim Preserve aryNames(1 To i)
Worksheets(aryNames).Select
End If
End With

vicks
02-10-2009, 06:38 AM
Thanks xld

Just wanted to check why cant the following formats work?

ThisWorkbook.Worksheets(array("ABCD*")).Select

Bob Phillips
02-10-2009, 07:19 AM
Because it doesn't.

Worksheets expects a name or an array of names for selection, and array("ABCD*") is not an array of all names beginning with ABCD, it is just a single item array of ABCD*.