PDA

View Full Version : [SOLVED] An Array of Sheet Names



simora
03-15-2018, 09:54 PM
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
03-15-2018, 10:24 PM
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
03-16-2018, 07:51 AM
Array Note: YasserKhalil's array's indices run from 0 to Number of Weekday Sheets minus 1

Paul_Hossler
03-16-2018, 01:35 PM
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

21851


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
03-16-2018, 03:53 PM
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
03-16-2018, 04:11 PM
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
03-16-2018, 09:55 PM
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.