MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- Windows
Hi,
I currently have this to list all sheetnames in the workbook I'm currently in:
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
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: