Worksheet arrays VBA
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 |
Worksheet arrays VBA
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 -- __________________________________ HTH Bob "Phil G" wrote in message ... 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 |
Worksheet arrays VBA
Bob - thanks for that - code is exactly what I was looking for - "Split" -
I'll remember that. Ta - Phil "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Phil G" wrote in message ... 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 |
All times are GMT +1. The time now is 03:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com