Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 10th 08, 06:36 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default Printing Sheet's Name

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.

  #2   Report Post  
Old November 10th 08, 06:49 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default Printing Sheet's Name

1. Insert a new sheet and name it SheetList
2. Press ALT-F11 to open VB Editor
3. Click Insert|Module
4. Paste the code below
Sub sheetList()
Dim i As Integer
i = 1
For Each ws In Worksheets
Sheets("SheetList").Cells(i, 1).Value = ws.Name
i = i + 1
Next
End Sub
5. Press F5
You will get the list in the new sheet... (including SheetList)
Press the

"Gingit" wrote:

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.

  #3   Report Post  
Old November 10th 08, 07:15 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default Printing Sheet's Name

Sheeloo,

After I press F5 it gave me a run time error subscript out of range.

Thanks.

"Sheeloo" wrote:

1. Insert a new sheet and name it SheetList
2. Press ALT-F11 to open VB Editor
3. Click Insert|Module
4. Paste the code below
Sub sheetList()
Dim i As Integer
i = 1
For Each ws In Worksheets
Sheets("SheetList").Cells(i, 1).Value = ws.Name
i = i + 1
Next
End Sub
5. Press F5
You will get the list in the new sheet... (including SheetList)
Press the

"Gingit" wrote:

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.

  #4   Report Post  
Old November 10th 08, 07:44 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default Printing Sheet's Name

How many sheets do you have?
Change
Dim i As Integer
to
Dim i As Long

and try

"Gingit" wrote:

Sheeloo,

After I press F5 it gave me a run time error subscript out of range.

Thanks.

"Sheeloo" wrote:

1. Insert a new sheet and name it SheetList
2. Press ALT-F11 to open VB Editor
3. Click Insert|Module
4. Paste the code below
Sub sheetList()
Dim i As Integer
i = 1
For Each ws In Worksheets
Sheets("SheetList").Cells(i, 1).Value = ws.Name
i = i + 1
Next
End Sub
5. Press F5
You will get the list in the new sheet... (including SheetList)
Press the

"Gingit" wrote:

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.

  #5   Report Post  
Old November 10th 08, 07:55 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 53
Default Printing Sheet's Name

Use this formula to show the sheet name.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

advantage: no macro is required here
disdvantage: will not show the name properly till the file is saved.

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Gingit" wrote:

Sheeloo,

After I press F5 it gave me a run time error subscript out of range.

Thanks.

"Sheeloo" wrote:

1. Insert a new sheet and name it SheetList
2. Press ALT-F11 to open VB Editor
3. Click Insert|Module
4. Paste the code below
Sub sheetList()
Dim i As Integer
i = 1
For Each ws In Worksheets
Sheets("SheetList").Cells(i, 1).Value = ws.Name
i = i + 1
Next
End Sub
5. Press F5
You will get the list in the new sheet... (including SheetList)
Press the

"Gingit" wrote:

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.



  #6   Report Post  
Old November 10th 08, 08:06 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 63
Default Printing Sheet's Name

Other solution: install the ASAP UTILITIES!!!
Have a Google on internet en download free!

Have fun!

--
met vriendelijke groetjes


"Gingit" schreef in bericht
...
I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.



  #7   Report Post  
Old November 10th 08, 08:49 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 53
Default Printing Sheet's Name

For the macro,
Sheeloo's answer was good

I've just removed the sheet name and it should work properly now.,

Sub sheetList()

Dim i As Integer
i = 1
For Each ws In Worksheets
Cells(i, 1).Value = ws.Name
i = i + 1
Next

endsub

Now. press f5 to execute the macro
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Satti Charvak" wrote:

Use this formula to show the sheet name.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

advantage: no macro is required here
disdvantage: will not show the name properly till the file is saved.

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Gingit" wrote:

Sheeloo,

After I press F5 it gave me a run time error subscript out of range.

Thanks.

"Sheeloo" wrote:

1. Insert a new sheet and name it SheetList
2. Press ALT-F11 to open VB Editor
3. Click Insert|Module
4. Paste the code below
Sub sheetList()
Dim i As Integer
i = 1
For Each ws In Worksheets
Sheets("SheetList").Cells(i, 1).Value = ws.Name
i = i + 1
Next
End Sub
5. Press F5
You will get the list in the new sheet... (including SheetList)
Press the

"Gingit" wrote:

I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.

  #8   Report Post  
Old November 10th 08, 09:13 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default Printing Sheet's Name

Thank you all it worked great!!!



"Frederik" wrote:

Other solution: install the ASAP UTILITIES!!!
Have a Google on internet en download free!

Have fun!

--
met vriendelijke groetjes


"Gingit" schreef in bericht
...
I have a spreadsheet with a lot of sheets and wanted to know
Is there anyway to print only the sheet names
Thanks.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy a sheet's formatting from one xls to another? John Dalberg Excel Discussion (Misc queries) 2 October 16th 07 04:18 PM
function to use sheet's name as variable tibby Excel Worksheet Functions 3 February 8th 07 05:50 PM
how to get sheet's number it's name alekm Excel Discussion (Misc queries) 2 January 30th 07 11:32 AM
Check if sheet's name is a number linglc Excel Discussion (Misc queries) 1 July 13th 06 01:48 AM
Tab Name Shown In Another Sheet's Cell jbtenor1 Excel Discussion (Misc queries) 1 October 26th 05 08:52 PM


All times are GMT +1. The time now is 07:09 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017