Macro to Create Button and Assign ClearFilters Command in a Different Workbook

I have an analysis workbook with multiple tabs that is too large to share with a broader audience and so I have created a macro which summarizes the results of the analysis on a new tab, copies that tab to a new workbook and then saves that workbook under a new name. As a part of the macro I create two buttons....the first button clears filters from the worksheet and the second button selects a defined range and prompts the sort command.

My problems is that when the sheet copies to a new workbook it retains the buttons but loses the connection to the underlying macros that were assigned to them....is there a way to either 1) export the module/macro with the sheet as it is copied to a new workbook or 2) to have the code write the macro in the new workbook so that it can be assigned to the buttons. Any help with this question would be greatly appreciated.

Here is a copy of the code I use to reset filters and prompt the sort command.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi hurst2008,

The following Macro may help you get started. It identifies Macros (associated with Shapes) that refer to another workbook, and replaces the reference with a reference (actually no reference) to the workbook that is running the code. See the attached file that implements the Macro.

My speculation is that someone else has a much simpler solution for your problem. I have used this and similar code for references in chart data and formulas.

Lewis

Please Login or Register  to view this content.