List of sheet names

[sevas1994]

Dear all,

How can I get a list of all sheet names written on a range A1:A234?

Thanks in advance

 

[AliGW]

1. In Names Manager on the Formulas ribbon, create a new name called SheetNames and type this into the reference box at the bottom: =GET.WORKBOOK(1)&T(NOW())
2. In A1 type this and copy down: =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)),"")

 

[AliGW]

I should have said that you may have to save the workbook as macro-enabled (.xslxm) - I hope that's not an issue for you.

 

[sevas1994]

Many thanks for a quick reply. It worked without having to save the file as a macro enabled workbook

1. In Names Manager on the Formulas ribbon, create a new name called SheetNames and type this into the reference box at the bottom: =GET.WORKBOOK(1)&T(NOW())
2. In A1 type this and copy down: =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)),"")

 

[AliGW]

Good to know - very glad to have helped!

 

[Fishboy]

It worked without having to save the file as a macro enabled workbook

Good to know - very glad to have helped!

Technically macros should work in a non-macro enabled workbook, but if you don't save the workbook as a macro enabled then the code just gets stripped out upon saving.

 

[sevas1994]

Yep I only realized it when I saved the file as a regular Excel file rather than a macro enabled one and then the formulas returned blanks. Also the get.workbook(1) formula that I wrote in named range was deleted.

Technically macros should work in a non-macro enabled workbook, but if you don't save the workbook as a macro enabled then the code just gets stripped out upon saving.

 

[AliGW]

Thanks for the update, which confirms my suspicions.