An Array of Sheet Names

[simora]

Hi:

I am trying to create an array of sheetnames in a workbook using vba.
The sheetnames are days of the week, but some weeks only have specific week days, Not all seven.

If I border the sheetnames in the Workbook with two blank sheets, First & Last, how can I create an array of the sheetnames that fall between those two blank sheets, First & Last.
( Maybe only Sunday to Wednesday ) for that week.

The workbook has other sheets.

I'm calling the array DaysArray

If there's a better way to do this, I'm interested.

Thanks

[YasserKhalil]

Hello
Try this code

Sub Test()
    Dim a()         As Variant
    Dim f           As Integer
    Dim l           As Integer
    Dim i           As Integer
    Dim k           As Integer


    f = ThisWorkbook.Sheets("First").Index
    l = ThisWorkbook.Sheets("Last").Index


    For i = f + 1 To l - 1
        ReDim Preserve a(k): k = k + 1
        a(UBound(a)) = ThisWorkbook.Sheets(i).Name
    Next i
End Sub

[SamT]

Array Note: YasserKhalil's array's indices run from 0 to Number of Weekday Sheets minus 1

[Paul_Hossler]

Rather than bracket with 2 worksheets, I'd multi-select the tabs

Click the first one (Mon) and Shift-Click the last one (Sun) -- Note there's no Wed

Capture.JPG


You can also Control-Click to select/deselect one by one

Option Explicit

Sub test()
    Dim A() As String
    Dim B() As Worksheet
    Dim i As Long
    
    Dim ws As Worksheet
    
    ReDim A(1 To ActiveWindow.SelectedSheets.Count)
    ReDim B(1 To ActiveWindow.SelectedSheets.Count)
    
    i = 1
    For Each ws In ActiveWindow.SelectedSheets
        A(i) = ws.Name
        Set B(i) = ws
        i = i + 1
    Next
    For i = LBound(A) To UBound(A)
        MsgBox A(i)
    Next i

End Sub

[simora]

Thanks all.

My original post included an error. This should have read " how can I create an array of the VISIBLE sheet names that fall between those two blank sheets, First & Last. "

It's the VISIBLE sheet names that I wanted to include in the array, NOT all sheet names.

Since the day names might change frequently, I wanted this to be as painless as possible.

Thanks

[Paul_Hossler]

Is there a reason why you want to create 2 empty "book mark" sheets?

The .SelectedSheets in #4 only does the visible sheets, and doesn't require 2 extra sheets to be added

[simora]

Thanks. You guys pointed me in the right direction.
After sleep, search & Thinking, I thought of a slightly different way to solve the problem.
I included only the Visible sheets in the array.
This code helped.

 j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Visible = True Then
            ReDim Preserve myArray(j)
            myArray(j) = i
            j = j + 1
        End If
    Next i

Thanks for all your help & suggestions.