Tab names to a list?

[StargateFanNotAtHome]

Is there a way to get a printout of just the names on the tabs of each
worksheet in a workbook? I have one that has a ton of them and it
would be just so much easier to get a printout than try to copy each
name manually.

Is there any way?

Thanks. D

 

[Don Guillett]

This sub will make a list in col F

Sub listshtnames()
For i = 1 To Sheets.Count
Cells(i, "f") = Sheets(i).Name
Next i
End Sub

 

[StargateFanNotAtHome]

This sub will make a list in col F

Sub listshtnames()
For i = 1 To Sheets.Count
Cells(i, "f") = Sheets(i).Name
Next i
End Sub

Hey, that's neat. I can see that this will do the job.

However, I got this error:
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Can't find project or library
---------------------------
OK Help
---------------------------

and it highlights the "i" in "For i = 1 To Sheets.Count".
Is there a way to fix that error?

Also, though I'm not sure of the code, went to the newsgroup via
google and found some lines of coding I'd like to add so that the
information will go on a blank sheet. I'm putting this into the
PERSONAL.XLS and want to be able to use it for any situation so col F
might have something in it already.

Also, perhaps the error comes from calling this from PERSONAL.XLS(?).

Thanks! D

 

[StargateFanNotAtHome]

This sub will make a list in col F

Sub listshtnames()
For i = 1 To Sheets.Count
Cells(i, "f") = Sheets(i).Name
Next i
End Sub

Hey, that's neat. I can see that this will do the job.

However, I got this error:
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Can't find project or library
---------------------------
OK Help
---------------------------

and it highlights the "i" in "For i = 1 To Sheets.Count".
Is there a way to fix that error?

Also, though I'm not sure of the code, went to the newsgroup via
google and found some lines of coding I'd like to add so that the
information will go on a blank sheet. I'm putting this into the
PERSONAL.XLS and want to be able to use it for any situation so col F
might have something in it already; will this work ... :
*********************************************
Sub SheetNames_ListAllInBlankSheet()
ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "SHEETNames"
For i = 1 To Sheets.Count
Cells(i, "A") = Sheets(i).Name
Next i
End Sub
*********************************************

Also, perhaps the error comes from calling this from PERSONAL.XLS(?).

Thanks! D

 

[Don Guillett]

You should test your macro yourself. Should work just fine.

 

[StargateFanNotAtHome]

You should test your macro yourself. Should work just fine.

It doesn't work. That's why I posted it.

It gets stuck on the same problem at the "i" as I posted before.

If it didn't get stuck, I obviously (obviously), could have tested it!
<vbg>

So what is causing that error, pls, anyone know?

Thanks! D

 

[Don Guillett]

I put your code in my personal.xls workbook>saved it>hid it>executed the
macro in another file. Worked just fine......

 

[StargateFanNotAtHome]

Does anyone know why this would happen?

Thanks. D

 

[Pete_UK]

Copy and paste your macro here, so we can see if there is a simple
typo or something.

Pete

 

[StargateFanNotAtHome]

Copy and paste your macro here, so we can see if there is a simple
typo or something.

Hi!

It's the code I put in the same message:

Thanks! D

 

[Dave Peterson]

Inside the VBE
Click on Tools|References
Scroll down that list looking for MISSING
Uncheck that item

 

[Frederik]

Just install the ASAP-utilities.
Then:

Information
List all sheet names

Voila!!!!

Success

 

[StargateFanNotAtHome]

Inside the VBE
Click on Tools|References
Scroll down that list looking for MISSING
Uncheck that item

Hi, thanks! The only missing item I had seemed to be completely
irrelevant to me; just shows how much one knows, after all. It was a
missing calendar control. But once I unticked that, sure enough, this
script worked. Go figure <g>.

Thanks! D

[snip]

 

[StargateFanNotAtHome]

Hi, thanks! The only missing item I had seemed to be completely
irrelevant to me; just shows how much one knows, after all. It was a
missing calendar control. But once I unticked that, sure enough, this
script worked. Go figure <g>.

Thanks! D

[snip]

I modified the script to this:
************************************************
Sub SheetNames_ListAllSheetNamesIntoABlankSheet()
'
ActiveSheet.Unprotect 'place at the beginning of the code
ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "SHEETNames"
For i = 1 To Sheets.Count
Cells(i, "A") = Sheets(i).Name
Next i
Columns("A:A").EntireColumn.AutoFit
ActiveCell.Offset(1, 0).Select
End Sub
************************************************

It seems to do everything perfectly fine though more testing may
reveal problems, for all I know. But at this point, so far, so good.

The only thing I'd like to add is the instruction to Excel to create a
sheet even if one called "SHEETNames" already exists. This is in case
any of the users accessing this workbook runs the macro not realizing
that one is already there. I think I know how to add a message box
telling user SHEETNames already exists (I'll hunt through my tips
folder; I believe I have something there to do that). But it would
still be handy to have Excel do something like this if the macro is
run, say, 5 times:

SHEETNames
SHEETNames NewCopy 1
SHEETNames NewCopy 2
SHEETNames NewCopy 3
SHEETNames NewCopy 4

I think above naming system is good enough and is self-explanatory
enough that the user should get it <g>.

What can be added to code above to do this, though, pls?

Thanks much in advance. D

 

[Dave Peterson]

I'd just delete any existing sheet and create the new sheet each time.

Option Explicit
Sub SheetNames_ListAllSheetNamesIntoABlankSheet()

dim i as long

on error resume next 'in case it's not there
application.displayalerts = false 'don't prompt me
worksheets("sheetnames").delete
application.displayalerts = true
on error goto 0

ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "SHEETNames"

For i = 1 To Sheets.Count
Cells(i, "A").value = "'" & Sheets(i).Name
Next i

Columns("A:A").EntireColumn.AutoFit

End Sub

If you had a sheet named 1.0 or 1/1/2008, you're better off storing the sheet
name as text. That's what the leading apostrophe does.

Alternatively:

For i = 1 To Sheets.Count
cells(i, "A").numberformat = "@" 'text
Cells(i, "A").value = Sheets(i).Name
Next i

Hi, thanks! The only missing item I had seemed to be completely
irrelevant to me; just shows how much one knows, after all. It was a
missing calendar control. But once I unticked that, sure enough, this
script worked. Go figure <g>.

Thanks! D

[snip]

I modified the script to this:
************************************************
Sub SheetNames_ListAllSheetNamesIntoABlankSheet()
'
ActiveSheet.Unprotect 'place at the beginning of the code
ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "SHEETNames"
For i = 1 To Sheets.Count
Cells(i, "A") = Sheets(i).Name
Next i
Columns("A:A").EntireColumn.AutoFit
ActiveCell.Offset(1, 0).Select
End Sub
************************************************

It seems to do everything perfectly fine though more testing may
reveal problems, for all I know. But at this point, so far, so good.

The only thing I'd like to add is the instruction to Excel to create a
sheet even if one called "SHEETNames" already exists. This is in case
any of the users accessing this workbook runs the macro not realizing
that one is already there. I think I know how to add a message box
telling user SHEETNames already exists (I'll hunt through my tips
folder; I believe I have something there to do that). But it would
still be handy to have Excel do something like this if the macro is
run, say, 5 times:

SHEETNames
SHEETNames NewCopy 1
SHEETNames NewCopy 2
SHEETNames NewCopy 3
SHEETNames NewCopy 4

I think above naming system is good enough and is self-explanatory
enough that the user should get it <g>.

What can be added to code above to do this, though, pls?

Thanks much in advance. D

 

[Tangier]

Is there a way to get a printout of just the names on the tabs of each
worksheet in a workbook?  I have one that has a ton of them and it
would be just so much easier to get a printout than try to copy each
name manually.

Is there any way?

Thanks.  D

print screen and just crop the image to capture only the tabs