Retrieve the name of worksheets in a workbook to a specific cell!!!!!!

[Mark McInerney]

Hi All,

I have 52 sheets, and I want to run a macro that I found online to retrieve the name of all the worksheets and place them in a specific row or column (not fussy - just need the data).

I found this, and press ALT +F11, paste it into the immediate pane, but nothing happens??

Am I doing something wrong - is there an easier way of retrieving the info that I am looking for?? Any Help - Appreciated. Thanks - Mark.

Sub ListSheetNames()
*** Dim R As Range
*** Dim WS As Worksheet
*** Set R = ActiveCell
*** For Each WS In ThisWorkbook.Worksheets
******* R.Value = WS.Name
******* Set R = R(2, 1)
*** Next WS
End Sub

 

[RickXL]

Hi,

Not sure about the Immediate window, but if you paste the code into a new Module and click run (F5) it should work.

Sub ListSheetNames()
    Dim R As Range
    Dim WS As Worksheet
    Set R = ActiveCell
    For Each WS In ThisWorkbook.Worksheets
        R.Value = WS.Name
        Set R = R(2, 1)
    Next WS
End Sub

 

[Mark McInerney]

Hi Rick,

Thanks for coming back to me on this - appreciated. Silly Question.....how do I create a new module??

Thanks - Mark.

Hi,

Not sure about the Immediate window, but if you paste the code into a new Module and click run (F5) it should work.

Sub ListSheetNames()
    Dim R As Range
    Dim WS As Worksheet
    Set R = ActiveCell
    For Each WS In ThisWorkbook.Worksheets
        R.Value = WS.Name
        Set R = R(2, 1)
    Next WS
End Sub

 

[RickXL]

Go to the VB Editor (Alt + F11)
From the menu: Insert-->Module
then paste.

Here is a better guide from Ron de Bruin: Where do I paste the code that I find on the internet

 

[Mark McInerney]

Thank's for your help on this Rick - really appreciate it.

Go to the VB Editor (Alt + F11)
From the menu: Insert-->Module
then paste.

Here is a better guide from Ron de Bruin: Where do I paste the code that I find on the internet

 

[RickXL]

No problem. I am pleased you got it working.

Regards,