Macro to copy data from other workbook - let user choose which open workbook

Hi there,

I receive sale handover workbooks from Sales on a daily / weekly basis, using a set template. I have a central workbook which is used to track these as they come in, recording things like client name, value etc.

I am building a macro to automatically import these kinds of details from each handover workbook into the central tracking workbook.. I need the macro to present the user with a list of all open workbooks, then allow the user to choose which open workbook they want to copy data from.

I can see how to obtain the name of the current open workbook, or how to select a workbook using a file>open type dialog, but that would cause problems when the workbookyou want is stored in a Lotus Notes or SharePoint database. Hence my desire to have the user open the handover workbook first, then run the macro from the tracking workbook to choose which of the other open workbooks to copy data from.

Any thoughts, please?
Thanks!

ssu95bm

Hi ssu95bm and welcome to the forum,

How you want to present the open workbooks and have your users select one is my question. To find all open workbooks you can use the index method shown in the example on:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=443

The example above asks for a specific workbook name by going through all opened workbooks. You can change the code a bit to list all open workbooks.

Hi,

I'd put the list on the sheet in cells and then do a validation list from it.

I have no idea how your user interface works but validation lists are a lot easier than building userforms.