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

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?

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

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!

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

if you need more help tailoring then shout!

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!

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.

have a look and see what you think.

that's great. though it seems like i would have to know the name of the open file. the problem with that is that it changes every time the data is downloaded. it isn't to say i dont know the name of the file, however. i'm just not sure if that will be a problem because i dont quite follow the approach, but will take a look when you finish with the skeleton.

how will the code run? i see it in the vba editor and i'll have to take a longer/closer look at it to figure out exactly what's going on, but i get the gist of it. just wondering how i would implement it?

Apologies you'll need to save this file, slight error in the last. Not tested!

save the addin.

Then in excel click tools>addins.

Click browse and navigate to the saved addin. click ok.

A menu item at the top of the screen appears called 'Download'.
select it and click format now.

sweet, thanks! i'll poke around, but will let you know if i run into any trouble. thanks so much!