Populate Rows from worksheet Names.

T

Trever B

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
 
N

Norman Jones

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).
Click to expand...
The trick is I dont know how many worksheets there are.
Click to expand...

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
'<<=============
 
J

JMB

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
 
Top