Need VBA to retreive worksheet names based on open workbooks

[The Exceller]

Appreciate any help:

I am pretty novice when it comes to vba but understand how some codes work.
I am searching for a vba code that I can plug in and it will list all worksheet names that come from an open workbook.

Can someone share a code with me that lists all worksheet names of a workbook based on using the "indirect" function?

For example if I type in '[File Name.xls] into a cell and want to use that as my reference for retreiving the worksheet names out of '[File Name.xls] what would be the code? Sheet.name somehow?

I would like the code to list all worksheet names of the '[File Name.xls] workbook in a current open separate workbook.


Any help is greatly appreciated.

[humdingaling]

dont really understand what you are trying to do as you are asking several questions at once but heres a simple code i've used before for sheetnames in a workbook

Please Login or Register  to view this content.

it will return to you the sheetnames in the cells on your first sheet A1, a2, a3 etc (depending on how many sheets you have)

indirect link to workbooks vary greatly depending if you have the workbook open or not

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[The Exceller]

Humdingaling, I cannot get your code to work but you are on the right track with the indirect link you're talking about.

John H. Davis, you code is fantastic and very useful. The one refinement I would need is to figure out how that can be automatic. I tried your code and it does exactly what I need except for I'm looking for a way that once a user enters the name of a workbook into my template (a spot I've already set aside), your code would automatically run and list the worksheets, as it currently does. In other words, is there a way that instead of it being an input box, the worksheets of a particular workbook name (that is typed in a cell) could appear on a separate sheet in that workbook.

For example on worksheet A of the "Template" workbook, the user types the name of a separate workbook they just opened, let's call it "Opened Workbook 1". Once they type in "Opened Workbook 1" in the "Template" workbook, worksheet B in the "Template" workbook lists all the worksheets coming from "Opened Workbook 1" just as your code does but without going through the input box step.

I am really impressed with your code, just need to change the paramaters a little.


Humdingaling, I am using the indirect formula to correct the #ref! based on the list of worksheet names and workbook name that is used with relation to the above. Hopefully that makes sense.

Thank you guys so much!
The Exceller

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

You would type in the workbook name in Range("A1"). Code would go in the sheet A event module of the "Template" workbook.

[The Exceller]

Hi John,

Thanks so much. I know I'm doing something wrong but I can't get it to function. To try the code, I have opened a new workbook fresh from excel and have entered your code into "sheet1" then on "sheet2" I am typing the name of another open workbook I have. When I go back to "sheet1" the worksheet names of the open workbook I have are not appearing. I have attached an example that will help you to demonstrate. You are a huge help.

Thank you

[JOHN H. DAVIS]

If you want a Change to Sheet2 to be the event which triggers the macro, then the code needs to reside in Sheet2.

Please Login or Register  to view this content.

Note I set ws2 to Sheet1 for your destination. I also changed it back too a Change event versus a Selection Change Event.

[The Exceller]

John,
Thank you so much. You're a genius. You helped me tremendously and I really appreciate the time you put in to help. Can't thank you enough. I have added to your reputation.
Thanks,

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback and the reps. Sounds like you are satisfied with the result. If so please mark this thread as solved.