Once I was working on an Excel File which had more than 80 worksheets and I had to take print of all those sheets. Since the No. of sheets was very large there was possibility of missing one or two sheets while printing. So I looked for a way by which I can get names of all the sheets in a worksheets to keep check on the sheets for which I have taken print. There is no menu command or option in Excel for that (You can insert current sheet name but not all sheet names in one sheet using few functions) So I looked at VBA which can do wonders for you if you know how to use it. I created the attached code for this task. Since I have learned many things from this chandoo.org i thought I must share this with others too.
how this addin works:
This add in look for a sheet named "Sheetnames" in your file if its not found then it will create a sheet with the same name and will insert the name of all sheets in column A and will also create the links to the respective sheets so that you can move to respective sheet by clicking on its name.
If the sheet with the name "Sheetnames" is found in your file then it will insert the name of all sheets in active column of the worksheet.
We can create a custom button in excel to run the program. I have listed the steps you need to follow to create the button. (Platform: Excel 2010)
1. Download the attached excel addin file and and save it to desktop and then move it to following folder
C: drive > Users> “Folder which has Your User Name” > App data (this folder may be hidden so you may need to check the command “View hidden Folders” from folder options in Organize tab on upper left hand side > roaming > Microsoft > Addins
2. Once you move the addin file to the add in folder you need to activate Developer Tab in excel. Do it as follows:
open Excel > File> Options > Customize Ribbon > On right hand side There would be an unchecked option “Developer” > check it
3. Once you have activated the Developer Tab click on it and do the following:
Developer > Addins > you will find an unchecked option with the name of “Sheetnames1” which is the addin we have to use. Check it and Click ok.
4. Now the last step is to create a button for the Addin
File> options > Quick Access Toolbar> Choose Command From > Macros > you will see an option with the name of “Sheetnames1”. Double click on it or select and click “Add>>”
5. You will get the button on quick access toolbar for the add in and its ready to use
The link to download the addin is as follows:
https://www.box.com/s/28d00d0da8404290031d
how this addin works:
This add in look for a sheet named "Sheetnames" in your file if its not found then it will create a sheet with the same name and will insert the name of all sheets in column A and will also create the links to the respective sheets so that you can move to respective sheet by clicking on its name.
If the sheet with the name "Sheetnames" is found in your file then it will insert the name of all sheets in active column of the worksheet.
We can create a custom button in excel to run the program. I have listed the steps you need to follow to create the button. (Platform: Excel 2010)
1. Download the attached excel addin file and and save it to desktop and then move it to following folder
C: drive > Users> “Folder which has Your User Name” > App data (this folder may be hidden so you may need to check the command “View hidden Folders” from folder options in Organize tab on upper left hand side > roaming > Microsoft > Addins
2. Once you move the addin file to the add in folder you need to activate Developer Tab in excel. Do it as follows:
open Excel > File> Options > Customize Ribbon > On right hand side There would be an unchecked option “Developer” > check it
3. Once you have activated the Developer Tab click on it and do the following:
Developer > Addins > you will find an unchecked option with the name of “Sheetnames1” which is the addin we have to use. Check it and Click ok.
4. Now the last step is to create a button for the Addin
File> options > Quick Access Toolbar> Choose Command From > Macros > you will see an option with the name of “Sheetnames1”. Double click on it or select and click “Add>>”
5. You will get the button on quick access toolbar for the add in and its ready to use
The link to download the addin is as follows:
https://www.box.com/s/28d00d0da8404290031d
