Let me explain. I am making a template file which will be reproduced many times. It relies upon a single data sheet and a bunch of central macros. All is well until I need to do a lookup in the single data sheet and return it to the template. I have to use the 'find' method for the lookup which, as i understand, means that I need to activate the data sheet. So far so good. So I get the value I want and now need to write it to the template file. But how? If i do workbooks("template.xls").activate, this works only for the template. As soon as the user saves it as something else it ceases to function.
I can write the full template path to a cell (i.e. it changes when filename changes) and use that. However, at some point I need to define that cell as a variable which means I have to refer to activeworkbook. By the time I get to using that variable, I am now in the data sheet and things fail - presumably because the macro looks up that currently active workbook, not the one I actually want.
That maybe sounds confusing but I suspect this is a problem easily sorted (afterall, templates are used everywhere!). Many thanks for the help.
Bookmarks