udf for returning names of wrkshts in active workbook
 

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

List all Sheets in the Book:
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

--
RyGuy


"Bassman62" wrote:

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

"ryguy7272" wrote:

List all Sheets in the Book:
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

--
RyGuy


"Bassman62" wrote:

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

Please click 'Yes', if the post was helpful to you.
--
RyGuy


"Bassman62" wrote:



"ryguy7272" wrote:

List all Sheets in the Book:
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

--
RyGuy


"Bassman62" wrote:

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to the
ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets
in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

I regret that my reply did not display. I'll try here.
Thank you for your suggestion.
I was hoping for a User Defined Function so that the reference would remain
updated in the event a worksheet name was changed. I suppose I could trigger
the procedure with a Change Event.
Thank you again for your reply.


"ryguy7272" wrote in message
...
List all Sheets in the Book:
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

List Sheets in the Book, 30 at a time, then shift over, list 30, repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name
iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If
Next
Range("A1").Select
End Sub

Regards,
Ryan---

--
RyGuy


"Bassman62" wrote:

Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets
in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

Tim,
Thank you for your reply. I am new to UDFs.
I pasted the function into a module in my workbook.
I'd like to call the function in order to list and refer to the worksheets.
I tried =GetWorksheets but I'm only getting a #Value error.
Can I call the function in cell formulas? How?
Thank you.

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to the
ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Using xl-2007;
Is there a UDF one could use to return all of the names of the worksheets
in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

This returns an array of worksheet names.

To use it, add the function to a regular module.
Then select 1:N cells, either in one row or one column, and type
=GetWorksheets() and press Ctrl + Shift + Enter which creates an array
formula. Be sure to select at least as many cells as there are worksheets or
the result will omit some names.

Function GetWorksheets() As Variant
Application.Volatile
Dim vRet As Variant, i As Integer, wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
ReDim vRet(0 To Application.Caller.Cells.Count - 1)
For i = 0 To UBound(vRet)
If i < wkb.Worksheets.Count Then
vRet(i) = wkb.Worksheets(i + 1).Name
Else
vRet(i) = CVErr(xlErrValue) ' More cells selected than sheets
End If
Next
If Application.Caller.Rows.Count = 1 Then
vRet = Application.Transpose(Application.Transpose(vRet))
Else
vRet = Application.Transpose(vRet)
End If
GetWorksheets = vRet
End Function

To programmatically add the formula:

Sub AddUDF()
With Range("A1").Resize(Worksheets.Count)
.FormulaArray = "=GetWorksheets()"
' .Value = .Value 'optional to convert to values
End With
End Sub


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Tim,
Thank you for your reply. I am new to UDFs.
I pasted the function into a module in my workbook.
I'd like to call the function in order to list and refer to the
worksheets.
I tried =GetWorksheets but I'm only getting a #Value error.
Can I call the function in cell formulas? How?
Thank you.

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to the
ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Using xl-2007;
Is there a UDF one could use to return all of the names of the
worksheets in
the active workbook?
Thanks.

udf for returning names of wrkshts in active workbook
 

This works wonderfully!
I've no idea how, so I shall enjoy disecting the code and hopefully gain
some insight.
I've tried for days to set this in motion and have found it difficult to
find applicable documentaion.
Thank you for your prompt and helpful replies.


"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
This returns an array of worksheet names.

To use it, add the function to a regular module.
Then select 1:N cells, either in one row or one column, and type
=GetWorksheets() and press Ctrl + Shift + Enter which creates an array
formula. Be sure to select at least as many cells as there are worksheets
or the result will omit some names.

Function GetWorksheets() As Variant
Application.Volatile
Dim vRet As Variant, i As Integer, wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
ReDim vRet(0 To Application.Caller.Cells.Count - 1)
For i = 0 To UBound(vRet)
If i < wkb.Worksheets.Count Then
vRet(i) = wkb.Worksheets(i + 1).Name
Else
vRet(i) = CVErr(xlErrValue) ' More cells selected than sheets
End If
Next
If Application.Caller.Rows.Count = 1 Then
vRet = Application.Transpose(Application.Transpose(vRet))
Else
vRet = Application.Transpose(vRet)
End If
GetWorksheets = vRet
End Function

To programmatically add the formula:

Sub AddUDF()
With Range("A1").Resize(Worksheets.Count)
.FormulaArray = "=GetWorksheets()"
' .Value = .Value 'optional to convert to values
End With
End Sub


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Tim,
Thank you for your reply. I am new to UDFs.
I pasted the function into a module in my workbook.
I'd like to call the function in order to list and refer to the
worksheets.
I tried =GetWorksheets but I'm only getting a #Value error.
Can I call the function in cell formulas? How?
Thank you.

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to
the ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Using xl-2007;
Is there a UDF one could use to return all of the names of the
worksheets in
the active workbook?
Thanks.