Microsoft Excel
doesn’t provide a direct way to generate a list of sheet names in a workbook. Here is a small macro to generate the list of all the sheets in a workbook.
First you open a workbook which contains lot of
worksheets and follow the steps
Step 1 : Insert
a new worksheet where you want the worksheet names to be displayed
Step 2 Goto –VIEW –MACROS-VIEW [Here I am using
Excel 2013 so the tabs are slightly different from the version you might be
using ;excel 2007 or Excel 2010 .But dont worry :D]
.
Step 3:In “Macro name”
column please write WorksheetName [This
is the name of the macro]
.
Step 4 Click On Create Button.You will Get a
sceen like this
.
Step 5 Paste
the following argument between “Sub
WorksheetName()” and “End Sub”
Dim x As
Integer
For x = 1 To
Worksheets.Count
Cells(x,
1).Value = Worksheets(x).Name
Next x
.
Step 6 :Close
the Macro window
Now the macro is ready to run .
How to Run The Macro
goto “VIEW”
Tab- MACROS-VIEW MACRO -RUN
For the macro aversed, you can create a new tab, and in cell A1 do a formula to copy cell A1 in the next tab by typing "=" and using the mouse or [Ctrl]+[PgDn], selecting cell A1 and hitting [Enter]. This action moves the active cell in your new tab to A2, enter the same formula to copy cell A1 in the next tab over. Repeat for every tab, entering a new formula working down the A column in the tab you created. Then do a Search and Replace to replace the "=" with nothing (make sure the replace with window is empty). Do another Search and Replace to remove the "!A1".
ReplyDelete