Is it possible to extract the name of all the worksheets within a spreadsheet.

On a monthly basis I have a spreadsheet that have a worksheets added. I
need to identify which worksheets are new.

In a nutshell I require a contents page on the first worksheeting listing
what all the worksheet names are with the spreadsheet.

You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968

Hi

Create an UDF (Activate VBA editor pressing Alt+F11, insert a new module
when there is no one in your workbook, and copy the code below into it).

Public Function TabI(TabIndex As Integer, Optional parVolatile As Date) As
String
TabI = Sheets(TabIndex).Name
End Function


On sheet you want to have the list of sheets in, into cell A1 enter the
formula
=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
, and copy it down for some number of rows.

In case you want a heading for sheets list in cell A1, enter the modified
formula into cell A2
=IF(ISERROR(TABI(ROW()-1,NOW())),"",TABI(ROW()-1))
, and again copy it down.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Sunny" wrote in message
...
Is it possible to extract the name of all the worksheets within a
spreadsheet.

On a monthly basis I have a spreadsheet that have a worksheets added. I
need to identify which worksheets are new.

In a nutshell I require a contents page on the first worksheeting listing
what all the worksheet names are with the spreadsheet.

The free Excel add-in "XL Extras" will do that.
Plus it will sort the workbook sheets and do other stuff.
Download from ... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA


"Sunny"

wrote in message
Is it possible to extract the name of all the worksheets within a spreadsheet.
On a monthly basis I have a spreadsheet that have a worksheets added. I
need to identify which worksheets are new.
In a nutshell I require a contents page on the first worksheeting listing
what all the worksheet names are with the spreadsheet.

Thank you all for your responses.

Much Appreciated

"Sunny" wrote:

Is it possible to extract the name of all the worksheets within a spreadsheet.

On a monthly basis I have a spreadsheet that have a worksheets added. I
need to identify which worksheets are new.

In a nutshell I require a contents page on the first worksheeting listing
what all the worksheet names are with the spreadsheet.

This works. Exactly what I wanted.

Thanks a lot saved a lot of hassle.

Sunny

"raypayette" wrote:


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968

A bit simpler.

Sub listsheets()
For i = 1 To Worksheets.Count
Cells(i, 1) = Sheets(i).Name
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fanny" wrote in message
...
Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I
copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny


"raypayette" wrote:


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile:
http://www.excelforum.com/member.php...o&userid=29569
View this thread:
http://www.excelforum.com/showthread...hreadid=566968

And in B3:

Sub SheetList()
dim i as long
for i = 1 to worksheets.count
activesheet.cells(i+2,"B").value = "'" & worksheets(i).name
next i
end sub

Sometimes plopping the worksheet name into a cell isn't enough. You'll want to
stop excel from parsing the entry. The "'" will treat the cell's value as text.

It would affect worksheets with names like:
000001
January 1, 2007
1.120000



Fanny wrote:

Dear Helpers,

I need to extract all the worksheet names in a summary worksheet. I copied
the following VBA from the Office Discussion Groups Q & A

Quote

Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub €“ by Mr Raypayette

Unquoted

The above vba works well but always have the result located at A1. Is it
possible to put it to B3 or any designated cell I choose.

Thanks in advance

Fanny

"raypayette" wrote:


You would have to use VBE:
Sub SheetList()
For Each ws In Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=566968



--

Dave Peterson