VBA - List All Sheetnames In A Different Open Workbook

[MixedUpExcel]

Hi,

I currently have this to list all sheetnames in the workbook I'm currently in:

Sub CreateAListOfAllWorksheets2() 

Application.DisplayAlerts = False

Dim mySh As Worksheet

    Columns(1).Insert
    
On Error Resume Next

Set mySh = Application.ActiveSheet

For i = 2 To Application.Sheets.Count

    mySh.Range("A" & (i - 1)) = Application.Sheets(i).Name
    
Next

Application.DisplayAlerts = True

End Sub

What I'm trying to do though, I've spent hours looking through Google and Forum's but can't seem to find what I need which is:

2 x Open Workbooks - Workbook A and Workbook B (both are open)

I am active in Workbook B

I want to be able to run a macro which will be in a module in Workbook B (similar to the one above) but instead of it listing all sheet names in my current active workbook Workbook B, I want to get all of the sheet names in Workbook A list them on my current page in Column A on Workbook B

To add to this, I currently have the name of Workbook A in Cell O4 on Sheet 'NOTES', so in Cell O4 it would have: Workbook A.xlsx

My thoughts are that I could get the list of sheet names and store them in an array and then take them from the array and paste them into Column A on my sheet in Workbook B

Any suggestions would be appreciated.

Thanks in advance.

Simon

 

[Fluff]

How about

Sub CreateAListOfAllWorksheets2()
   Dim mySh As Worksheet, Ws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   
   Set Wbk = Workbooks(ThisWorkbook.Sheets("Notes").Range("O4").Value)

   Columns(1).Insert
   For Each Ws In Wbk.Worksheets
      i = i + 1
      Range("A" & i).Value = Ws.Name
   Next
End Sub

 

[MixedUpExcel]

Thanks Fluff

PERFECT!

You always come up with shorter / quicker solutions

Thanks again.

Simon

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mikael_L]

Hi Fluff,

Sorry totally VBA newbie here, and no it is a old, thread..

2 questions.

- Is it possible to get the script running without having the other workbook open?

- How do I get the script just to insert the sheets name in row A, without adding a column

 

[Fluff]

For part1, No. The other workbook needs to be open.
For part2, remove this line Columns(1).Insert

 

[Mikael_L]

Thanks for this, thought I already tried that :/..

Maybe this is changeing the topicto much but I'll give it a shoot.

I use to above code to make a dropdown list of the diffrent sheets in the other workbook.

I've would then like to depending on what sheet that is chosen in the dropdown, to copy the data in B8:P90

Saying the dropdown will be be O5, And I'll like to paste the complete data into D6

 

[Fluff]

You will need to start a thread of your own for this. Thanks