not having a macro open the worksheet in which it was written

[johndough185]

I've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it. my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.

maybe the button thing just won't work, and they'll have to go to tools, macro?

[MattShoreson]

try creating an addin and using it as the code repository.

[johndough185]

i'm pretty new to the whole macro creation space. i wouldn't know how to create an add-in, though this is something that I thought would be handy. could you explain a little more in detail? thanks!

[MattShoreson]

check out http://www.exceltip.com/show_tip/Add...Excel/633.html

if you need more help tailoring then shout!

[johndough185]

i was tossing this idea around, but couldn't figure out a way to actually use the user defined function. the macro just formats the entire sheet, whereas it appears for a udf you need to specific inputs for the function. i would like to be able to just click the button and have the macro run. maybe i'm missing something regarding the comment about using the add-in as a code repository. i don't know what that means. thanks!

[MattShoreson]

you wont need the udf.

It's big but I personally would use this approach...

Use application.getopenfilname to retrieve the downloaded data file.
open said data file.
Use your existing code to format the data file.
Then you can use the save/save as.

Because the code doesnt reside in the template and you navigate to the efile each time, you shouldn't experience the problems you were getting.

give me 10 mins and I'll knock up the skeleton.