An Array of Sheet Names

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

Hello
Try this code

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

---

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

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

Attachment 21851


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

Code:

---

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

---

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

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

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.

Code:

---

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.