Sheet Names Array
 

Hi,

I need to create a function that returns an array of sheet tab names as
they appear on the screen in order from left to right. The array should
"exclude" the following two sheet names - "Addresses" and "CrossRef" and
should also exclude "hidden" sheets.

Does anyone have any ideas?
--
Regards,
Rocky McKinley

Sheet Names Array
 

Rocky,

Try a function like the following:

Function SheetArray() As String()
Dim Arr() As String
Dim ndx As Long
Dim WS As Worksheet
For Each WS In Worksheets
If WS.Visible = xlSheetVisible Then
If WS.Name < "Addresses" And WS.Name < "CrossRef"
Then
ndx = ndx + 1
ReDim Preserve Arr(1 To ndx)
Arr(ndx) = WS.Name
End If
End If
Next WS
SheetArray = Arr
End Function

You can then call this function like

Dim Arr() As String
Dim ndx As Long
Arr = SheetArray()
For ndx = LBound(Arr) To UBound(Arr)
Debug.Print Arr(ndx)
Next ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Rocky McKinley" wrote in message
...
Hi,

I need to create a function that returns an array of sheet tab
names as
they appear on the screen in order from left to right. The
array should
"exclude" the following two sheet names - "Addresses" and
"CrossRef" and
should also exclude "hidden" sheets.

Does anyone have any ideas?
--
Regards,
Rocky McKinley

Sheet Names Array
 

Perfect Chip, thank you very much

--
Regards,
Rocky McKinley


"Chip Pearson" wrote in message
...
Rocky,

Try a function like the following:

Function SheetArray() As String()
Dim Arr() As String
Dim ndx As Long
Dim WS As Worksheet
For Each WS In Worksheets
If WS.Visible = xlSheetVisible Then
If WS.Name < "Addresses" And WS.Name < "CrossRef"
Then
ndx = ndx + 1
ReDim Preserve Arr(1 To ndx)
Arr(ndx) = WS.Name
End If
End If
Next WS
SheetArray = Arr
End Function

You can then call this function like

Dim Arr() As String
Dim ndx As Long
Arr = SheetArray()
For ndx = LBound(Arr) To UBound(Arr)
Debug.Print Arr(ndx)
Next ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Rocky McKinley" wrote in message
...
Hi,

I need to create a function that returns an array of sheet tab
names as
they appear on the screen in order from left to right. The
array should
"exclude" the following two sheet names - "Addresses" and
"CrossRef" and
should also exclude "hidden" sheets.

Does anyone have any ideas?
--
Regards,
Rocky McKinley