PDA

View Full Version : Solved: Gather tab names from workbook



austenr
04-18-2007, 01:34 PM
Hi guys,

Havent posted in a while. Is there a way to output the tab names in a workbook either to a worksheet or hard copy? Have a workbook with around 80 sheets and I need a listing of what they are called. Thanks

mdmackillop
04-18-2007, 02:12 PM
Hi Austen,
Run this in an empty sheet.
Sub ShNames()
Dim sh, i As Long
For Each sh In Sheets
i = i + 1
Cells(i, 1) = sh.Name
Next
End Sub

omocaig
04-18-2007, 02:14 PM
I wrote this code a while back but I think it should work for you...

Sub MakeIndex()
'
' Creates a Table of Contents for the Workbook
' Giacomo Genovese: 2/17/2004

On Error GoTo err_make_index
Dim iteration As Integer
Dim hasIndex As Boolean
Dim result As VbMsgBoxResult
Const INDEX = "Index"

hasIndex = False

' Determine if Workbook already has an index
For Each sht In ActiveWorkbook.Sheets
If sht.Name = INDEX Then
hasIndex = True
Exit For 'exit loop if index already exists
End If
Next sht

If hasIndex Then
result = MsgBox("Overwrite the existing index?", vbYesNoCancel, "Create Index")
Else
' If index does not exist add a new sheet called Index
Sheets.Add
Sheets(1).Name = INDEX
result = vbYes
End If

Select Case result
Case vbYes
For Each sht In ActiveWorkbook.Sheets
iteration = iteration + 1
Sheets(INDEX).Range("a" & iteration).Select
If sht.Name <> "Index" Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sht.Name & "'!A1", TextToDisplay:=sht.Name
Else
Selection.Value = INDEX
Selection.Font.Bold = True
End If
Next sht
Case Else
Call MsgBox("No changes were made to the worksheet.", vbInformation, "Create Index")
Exit Sub
End Select

exit_make_index:
Exit Sub
err_make_index:
MsgBox (Err.Description)
End Sub

hth,
Giacomo

Shazam
04-18-2007, 03:56 PM
Here is a formula solution.

Go To:

Insert > Name > Define

Name: Sheets

Refers to:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Click Ok

Input formula in cell A2, copied down:

=INDEX(Sheets,ROWS($A$2:A2))


Hope it helps!

austenr
04-18-2007, 03:56 PM
Hi Malcomb and Gi,

Thanks to you both. Malcomb your solution best fits my needs but I will save G's code for future problems.

I dont know whether to start a new thread or not for an additional problem.

The 80+ worksheets are tables from an Access 97 DB (yes I said 97), which some of the columns will be converted to a new system. I know that there are some redundancies and would like to know if the following is possible:

Loop through each sheet in the workbook.

Within each sheet there is header data on row 1 of each column. I want to write that heading to a new sheet along with the data type and format in row2.

So basically loop the worksheets, read the column headings and data type. If it exists on the new sheet dont write it but if it doesnt write it.

Sound plausable? Or any other solution/suggestion would be appreciated.

mdmackillop
04-18-2007, 04:15 PM
Hi Austen,
I'm not clear what you mean by Data Type. Can you post a workbook with a couple of typical sheets?

austenr
04-18-2007, 04:23 PM
Oops. Instead of data type, I mean format. So say Sheet 1 has in A1 "Name", A2, has the name "Malcomb", which is text format. So on the new sheet on row 1 A1 would list Malcomb, while B1 would say "text". Etc. Cant really post a sample, its at work but can if you need more examples. Thanks.

austenr
04-19-2007, 09:55 AM
Here is a sample WB to help clarify the situation. Sheets 1 and 2 are what would be looped through and sheet 3 is the end result I want. Hope this helps.

Bob Phillips
04-19-2007, 10:17 AM
Option Explicit

Public Sub ProcessData()
Dim i As Long
Dim iLastCol As Long
Dim sh As Worksheet
Dim shNew As Worksheet
Dim iNextRow As Long

With ActiveWorkbook

Set shNew = .Worksheets.Add

For Each sh In .Worksheets

If sh.Name <> shNew.Name Then

iLastCol = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column
For i = 1 To iLastCol
iNextRow = iNextRow + 1
shNew.Cells(iNextRow, "A").Value = sh.Name
shNew.Cells(iNextRow, "B").Value = sh.Cells(1, i).Value
shNew.Cells(iNextRow, "C").Value = sh.Cells(2, i).Value
Select Case True
Case IsDate(sh.Cells(2, i).Value): shNew.Cells(iNextRow, "D").Value = "date"
Case IsNumeric(sh.Cells(2, i).Value): shNew.Cells(iNextRow, "D").Value = "number"
Case Else: shNew.Cells(iNextRow, "D").Value = "text"
End Select
Next i
End If

Next sh

End With

End Sub

austenr
04-19-2007, 10:25 AM
Spot on Bob!! Thanks. Solved.