VBA - List All Sheetnames In A Different Open Workbook

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
Hi,

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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Board Regular
Joined
Apr 7, 2015
Messages
206
Thanks Fluff

PERFECT!

You always come up with shorter / quicker solutions :)

Thanks again.

Simon
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Mikael_L

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
For part1, No. The other workbook needs to be open.
For part2, remove this line Columns(1).Insert
 

Mikael_L

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
You will need to start a thread of your own for this. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,128,157
Messages
5,629,023
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top