Sheet names

[chipgiii]

I often create excel files with numerous sheets. Each sheet gets a name, and when I am finished, I go back and create a table of contents on a new sheet. The TOC list each sheet name and describes what is on that sheet. Is there a function in excel or code that will allow me to create a list of existing sheet names with a click of the button.

Tired of typing!
Thanks in advance,
Chip

[stanleydgromjr]

chipgiii,

The following macro will create a list of sheetnames in the active worksheet beginning in cell A1.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the ListAllSheets macro.

[chipgiii]

Stan,
Thanks. Worked like a charm. I thought it was going to be much tougher than this. VBA is still a struggle for me after a month and a half.... This is perfect!

[stanleydgromjr]

chipgiii,

You are very welcome.

VBA is still a struggle for me after a month and a half

Training / Books / Sites:

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/ex...117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Y...Excel/631.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html

Excel Data Validation - Add New Items
http://www.contextures.com/excel-dat...ation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA, by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

DonkeyOte: My Recommended Reading:

Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=1...cel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Basics of array formulas
http://www.youtube.com/view_play_lis...7E7E9CA63304D3

Array formula data extract formulas
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

Unique Record Counting and Data Extract formulas
http://www.youtube.com/watch?v=uUrI8hoj8BA


And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com/
http://www.excelforum.com/
http://www.ozgrid.com/
http://www.vbaexpress.com/portal.php

[broro183]

...VBA is still a struggle for me after a month and a half....

Lol!
Only a month & a half - if you get the bug, you could have years ahead of you. Not necessarily of "struggle" but definitely full of challenges. I've got a few years under my belt & I'm still learning

Here's a small addition to Stanley's code to include hyperlinks:

Please Login or Register  to view this content.

I got the hyperlink line of code by recording a macro of my actions when I manually created a hyperlink using [ctrl + K], and then I modified the recorded code to use the ShtName variable.

Stanley,
Nice list of links

hth
Rob

[stanleydgromjr]

Rob,

Thanks.

The list just keeps getting longer and longer.

[JanHendrik]

I often create excel files with numerous sheets. Each sheet gets a name, and when I am finished, I go back and create a table of contents on a new sheet. The TOC list each sheet name and describes what is on that sheet. Is there a function in excel or code that will allow me to create a list of existing sheet names with a click of the button.

Tired of typing!
Thanks in advance,
Chip

I would like to take it a step further, by creating the TOC of the sheet names (quote reference) but I would like to inlcude the customer name that is listed on the specific worksheet next to the relevant sheet name in the TOC.

is there some one that have done this before and can show me how to write a macro for that?

Rgds,
JanHendrik

[stanleydgromjr]

JanHendrik,

I would suggest that you start a new Post.

In the new Post, please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

To attach your workbook in a new Post, click on the Advanced button, then scroll down and click on the Manage Attachments button.

[Coleen]

This is very helpful, thank you.

I am new to VBA as well and having difficulty trying to modify this code to:
1 - Start the list wherever the cursor is located
2 - List the sheet names as hyperlinks only

Thank you!

[FDibbins]

Hi Coleen and welcome to the forum

If you are just commenting on the previous posts, great

However, if you are asking questions regarding those posts, please note that unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.