MrExcel Publishing
Your One Stop for Excel Tips & Solutions

List of worksheet names


Posted by JAF on February 03, 2000 7:53 AM

I have a workbook which contains worksheets which are created when a DTS package in SQL Server moves data from the SQL database into Excel.

What I need is a quick and easy way of getting a list of all of the worksheet names in the workbook.

The workbook could contain anything from 20 to 124 different worksheet names each month.

Any assistance appreciated.
JAF


Posted by Ivan Moala on February 03, 2000 2:20 PM

Hi Jaf
try something like this;

Sub GetShNames()
Dim Sh
x as integer

For each Sh in Thisworkbook.Sheets
cells(x+1,1)=sh.name
x=x+1
next

End Sub

This will put the sheet name in the activesheet
beginning @ cell(1,1) = A1


Ivan
PS not tested


Posted by Mark on February 22, 2000 12:43 PM

Ivan

One small comment. Instead of using

Dim Sh
x as integer

Enter it as

Dim Sh, x as integer

(Sorry this is so late, but just looked at it.)

Mark


Posted by Ivan Moala on April 07, 2000 6:37 AM

Woops!!
Mark your right, forgot the Dim statement!!


Ivan