Populate Rows from worksheet Names.

Hi,

Thanks in advance.

Have a work sheet called summary.

In col B starting at row 11 I want to populate it with the names of
worksheets within the current file. (Excluding Summary).

The trick is I dont know how many worksheets there are.

Thanks

Trev

Hi Trev,

> In col B starting at row 11 I want to populate it with the names of
> worksheets within the current file. (Excluding Summary).

> The trick is I dont know how many worksheets there are.

Try:
'=============>>
Public Sub Tester()
Dim SH As Object
Dim i As Long

For Each SH In ThisWorkbook.Sheets
With SH
If UCase(.Name) <> "SUMMARY" Then
i = i + 1
Sheets("Summary").Range("B11")(i).Value = .Name
End If
End With
Next SH

End Sub
'<<=============


---
Regards,
Norman

You could use a macro to accomplish that.

Sub test()
Dim WkSht As Worksheet
Dim Count As Long
Dim rngTarget As Range

Set rngTarget = Worksheets("Summary").Range("B11")

For Each WkSht In Worksheets
If Not WkSht Is rngTarget.Parent Then
Count = Count + 1
rngTarget(Count, 1).Value = WkSht.Name
End If
Next WkSht
End Sub

"Trever B" wrote:

> Hi,
>
> Thanks in advance.
>
> Have a work sheet called summary.
>
> In col B starting at row 11 I want to populate it with the names of
> worksheets within the current file. (Excluding Summary).
>
> The trick is I dont know how many worksheets there are.
>
> Thanks
>
> Trev