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.