If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Macros in two different workbooks

This is probably very straightforward but it's got me stumped..

I have a blank questionnaire set up as an Excel template. I also have a completely separate workbook which I use to collate the information (text and numbers) gathered by the questionnaires. Until now I have done this manually but have written a button-operated macro in the questionnaire workbook which opens the other database file, adds the new data and sorts it alphabetically.

The problem is that I need to save the questionnaire (the Excel template) to my hard drive under an individual file name every time I complete one and if I don't want to export the data before saving, when I later retrieve the individual workbook and try to transfer the data using the macro,the macro looks for the template filename which, of course, has been overwritten. I have tried to use ThisWorkbook and ActiveWorkbook to sort it out but no luck so far.

Why don't you store the name of the workbook as a public variable if using a macro straight through without stopping.

If you have to reopen the workbook manually then instead of just opening it use application.getopenfilename, Storing the filename as a variable and trimming off the path by using a mid function to refrence the workbook.

Or when you open the workbook you could store the name of the workbook when opened immediatly by using variable = activeworkbook.name

or if you know what position the workbook is in you could refrence the workbook by its position.