Hi all,
First let me thank everyone for being so quick to respond and offer help on virtually every problem i run into with excel!
Here's my dilemma:
I have a master reporting template with combo boxes that allow the user to filter data, thus generating the report.
I want to create a button with a macro behind it that will:
1) Copy the entire sheet to a new worksheet, either in the same file, or if possible, in a file named "scorecard_X", where X equals the value selected in one of my combo boxes on the sheet (i.e. the year of the report)
2) Rename the worksheet based on the value of a cell on the sheet (i.e. the selected region). If a worksheet in the destination file already exists with this name, then a second copy of the worksheet should be created, e.g. Northeast, Northeast(2), Northeast(3). I don't want to overwrite the worksheets.
3) Paste values, removing all formulas
4) Remove all of the combo boxes, leaving just data
5) Save the file.
I think I could accomplish some by recording a macro of my actions, but I know I'll run into trouble when trying to make certain items dynamic, such as the naming convention for the files/worksheets.
Can anyone assist?
Thanks!!!
First let me thank everyone for being so quick to respond and offer help on virtually every problem i run into with excel!
Here's my dilemma:
I have a master reporting template with combo boxes that allow the user to filter data, thus generating the report.
I want to create a button with a macro behind it that will:
1) Copy the entire sheet to a new worksheet, either in the same file, or if possible, in a file named "scorecard_X", where X equals the value selected in one of my combo boxes on the sheet (i.e. the year of the report)
2) Rename the worksheet based on the value of a cell on the sheet (i.e. the selected region). If a worksheet in the destination file already exists with this name, then a second copy of the worksheet should be created, e.g. Northeast, Northeast(2), Northeast(3). I don't want to overwrite the worksheets.
3) Paste values, removing all formulas
4) Remove all of the combo boxes, leaving just data
5) Save the file.
I think I could accomplish some by recording a macro of my actions, but I know I'll run into trouble when trying to make certain items dynamic, such as the naming convention for the files/worksheets.
Can anyone assist?
Thanks!!!