Copy Paste sheet name to make list

**NMullis** · 03-02-2009, 01:54 PM

Good morning,
I would like to create a list of all the sheet names I have in a given workbook. Is there a simple way to do this through VBA?

Many thanks for your assistance!
N

**VBA Noob** · 03-02-2009, 02:10 PM

See link. Any problems post back

http://www.mvps.org/dmcritchie/excel/sheets.htm

VBA Noob

**NMullis** · 03-02-2009, 03:44 PM

This helps some, but perhaps I am not understanding or using the correct code from this link. I was able to copy the name of the sheet into cell A1 of the its own sheet, but not all names into one sheet. I hope that makes sense.

This is the code I used

Please Login or Register  to view this content.

But like I said, I maybe was using the wrong section as there is a lot covered there. Can you give me any futher direction to tell it copy the sheet name of each sheet into Sheet1 A1, A2, A3, etc?

Thanks so much.

**VBA Noob** · 03-02-2009, 03:51 PM

Try

Please Login or Register  to view this content.

VBA Noob

**NMullis** · 03-02-2009, 04:03 PM

I pasted this into a small workbook to test and it worked great. I pasted it into a big workbook (100+ sheets) and it ran but returned no results.

**VBA Noob** · 03-02-2009, 04:11 PM

Is there a sheet 1?

I tested on 21 sheets and worked fine

VBA Noob

**NMullis** · 03-02-2009, 04:13 PM

Picture me giving myself a major head slap! Sorry to waste your time there and thank you so much for the solutions!!

Is today Monday? N

**davidrias** · 02-21-2014, 04:17 PM

Hello, bringing this thread back from the dead. I am trying to do this same thing but when I run the code, i get an error saying Run-time error '1004'. The cell or chart you are trying to change is protected and therefore read-only.

None of the worksheets are protected, so I am not sure why I am getting this code?