I am creating "Macro 1" within "file 1" that opens "file X", creates a pivot table, formats the data, saves as "file 1" then creates a button (assigned macro is "Macro 1" from "file 1") and saves again.
I've played with saving before/after/during and seems that each time I save over "file 1" the macro module 1 disappears. Essentially I want to create a circular macro so that I never need to leave "file 1". I want file 1 to do all the work then save with the macro ready to go the next time.
Any thoughts on this? Is this possible?
My thought is I should be able to create a macro in "file 2", have a button in "file 1" to open the workbook "file 2" behind the scenes to do all the formatting and such, then save the formatted data as "file 1". Problem is, I need the button with "macro 1" to also save, but it does not. I would prefer to have the macro saved in the local workbook because this is a shared file. I would like to avoid having to share a macro file to each users XLSTART folder...
Any help is much appreciated, thank you.