Prev: Create Hyperlink in VBA
Next: Pull data (sumif?) from an external workbook
From: darsh on 17 Mar 2010 20:10
Hi,

Wondering how to create a list of pre-existing tabs in a workbook. I have a
book that has appx 100 tabs and I want to create a list of those tabs on one
sheet.

Is there an easier way then doing this manually? The tab names keep changing
every month and I need to get a quick inventory of what's in the book.

Many Thanks,
From: FSt1 on 17 Mar 2010 22:59
hi
Sub sheetnamesonSheet()
Dim r As Range
Dim rd As Range
Dim WS As Worksheet
Set r = Range("A2") 'change if needed. used sheet1 for demo
For Each WS In Worksheets
Set rd = r.Offset(1, 0)
r.Value = WS.Name
Set r = rd
Next WS
End Sub

goes in a standard module.

regards
FSt1


"darsh" wrote:

> Hi,
>
> Wondering how to create a list of pre-existing tabs in a workbook. I have a
> book that has appx 100 tabs and I want to create a list of those tabs on one
> sheet.
>
> Is there an easier way then doing this manually? The tab names keep changing
> every month and I need to get a quick inventory of what's in the book.
>
> Many Thanks,
From: Per Jessen on 17 Mar 2010 23:09
Hi

This will write all sheet names to a sheet named 'Name Sheet', except
'Name Sheet'

Sub NameList()
Dim DestSh As Worksheet
Dim DestCell As Range

Set DestSh = Worksheets("Name Sheet") 'Change to suit
Set DestCell = DestSh.Range("A1")

Range(DestCell, DestCell.End(xlDown)).ClearContents

For Each sh In ThisWorkbook.Sheets
If sh.Name <> DestSh.Name Then
DestCell = sh.Name
Set DestCell = DestCell.Offset(1, 0)
End If
Next
End Sub

Regards,
Per

On 18 Mar., 01:10, darsh <da...(a)discussions.microsoft.com> wrote:
> Hi,
>
> Wondering how to create a list of pre-existing tabs in a workbook. I have a
> book that has appx 100 tabs and I want to create a list of those tabs on one
> sheet.
>
> Is there an easier way then doing this manually? The tab names keep changing
> every month and I need to get a quick inventory of what's in the book.
>
> Many Thanks,

 | 
Pages: 1
Prev: Create Hyperlink in VBA
Next: Pull data (sumif?) from an external workbook