Copy/paste all worksheet names into one sheet

[contra76]

Hi all,

I'm having trouble figuring out how I would get all of the worksheet names in 1 workbook, copying and pasting them into cells in another sheet in that same workbook.

Example:
Sheet1 = Apple
Sheet2 = Pear
Sheet3 = Banana

Code would make extra sheet at end called "List" and would have cell values:
A1 = Apple
A2 = Pear
A3 = Banana

Please help? Thank you!

[stanleydgromjr]

contra76,

Welcome to the Excel Forum.


The macro will create a new worksheet Results, and the other sheet names will be listed in column A.


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.

[contra76]

Thank you!! This works amazingly. Made my life a lot easier

[lovecu]

Thanks for the same ...Its working very well

[stanleydgromjr]

lovecu,

You are very welcome. Glad I could help.

Come back anytime.

[skyfold]

Awesome! Thanks!

[stanleydgromjr]

skyfold,

Welcome to the Excel Forum.

Thanks for the feedback.

Come back anytime.

[Gyanu]

I also try this one. But I want similar with auto hyperlink function

[adeel g]

really cool macro.

thanks a lot Stanely.....

[stanleydgromjr]

adeel g,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

[dexboy]

Works like a charm! I wanted the data to be imported in A column on a tab but I figured, I don't need this so often so I did it manually afterwards

[stanleydgromjr]

Gyanu,

I also try this one. But I want similar with auto hyperlink function

Sorry I missed your request - here you go.


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).

1. Copy the below code, by highlighting the 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. Where the cursor is flashing, 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.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ListAllWorksheetLinks macro.

[alobaidis]

Awesome. Thank you SO much

[stanleydgromjr]

alobaidis,

Thanks for the feedback.

You are very welcome. Gald I could help.

Come back anytime.

[RogerWoo]

Hi, Stan. Thanks so much for making that idiot proof. Its really nice for you not to expect everyone who uses excel forum not to already be really good at macro writing. Some times people who do not use excel that much just need to get annoying problems sorted quick to hit a deadline. So I just wanted to say thanks for going the extra mile.



Roger

[stanleydgromjr]

RogerWoo,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

[mcnamaram]

This is a fabulous solution - The issue i have is that some of the worksheet names have spaces in the name and in this instance, when i activate the link i get an error message "REFERENCE IS NOT VALID" - can this be overcome or do i need to change my naming structure?

Thanks

[stanleydgromjr]

mcnamaram,

Welcome to the Excel Forum.

This is a fabulous solution

Thank you very much.

The issue i have is that some of the worksheet names have spaces in the name

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[Hank025]

Quite Possibly One of the Most Helpful Threads on the Internet! THANKS!!!!