Thank you for this thread, It has been a great help to me. I have a little extension of the problem though.
I have a spreadsheet which calls on data from SQL which my I.T guy set up, and it then populates information on sheets which have been created from running a previous macro. (If that doesn't make sense, let me know).
Then I actually want the function of being able to call the macro on these new sheets however it won't copy the call function into the new sheets.
I currently have a macro button on the original "template" doc, which is copied to the new sheets, however it'd be nice if I could get rid of these buttons and have the macro run on a change.
Is there any way of doing this or am I asking too much of excel??