Tab names to a list?

  • Thread starter Thread starter StargateFanNotAtHome
  • Start date Start date
S

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. :oD
 
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
 
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
Click to expand...

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! :oD
 
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
Click to expand...

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! :oD
 
You should test your macro yourself. Should work just fine.
Click to expand...

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! :oD
 
I put your code in my personal.xls workbook>saved it>hid it>executed the
macro in another file. Worked just fine......
 
Copy and paste your macro here, so we can see if there is a simple
typo or something.

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

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! :oD

[snip]
 
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! :oD
Click to expand...

[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. :oD
 
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! :oD
Click to expand...

[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. :oD
Click to expand...
 
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.  :oD
Click to expand...

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