Worksheet arrays VBA

P

Phil G

I'm trying to progamitically select a group of sheets with "CC" as part of
the name, they are always in one block but may vary in the collection of
sheets. I can find their start and finish index no's but how do I use this
in the "Sheets.Array" command - see code below.

Sub Test()
Dim WKS
Dim intStart As Byte
Dim intEnd As Byte
Dim intcnt As Byte

intStart = 0
intEnd = 0

For Each WKS In Worksheets
If InStr(UCase(WKS.Name), "CC") > 0 Then
If intStart = intEnd Then
intStart = WKS.Index
Else
intEnd = WKS.Index
End If
End If
Next

MsgBox "Start = " & intStart & " End = " & intEnd

For intcnt = intStart To intEnd

'now how to I program this with the above info?????
Sheets(Array("cc-1", "cc-2", "cc-3", "cc-4")).Select

Next

End Sub
 
B

Bob Phillips

Sub Test()
Dim WKS
Dim SheetNames As String
Const SHEET_ID As String = "SHEET"


intStart = 0
intEnd = 0

For Each WKS In Worksheets
If InStr(UCase(WKS.Name), SHEET_ID) > 0 Then
SheetNames = SheetNames & WKS.Name & ","
End If
Next
If SheetNames <> "" Then SheetNames = Left$(SheetNames,
Len(SheetNames) - 1)

MsgBox SheetNames

Sheets(Split(SheetNames, ",")).Select

End Sub
 
P

Phil

Bob - thanks for that - code is exactly what I was looking for - "Split" -
I'll remember that.

Ta - Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top