Need macro to copy data to new sheet & name worksheet

[psulion01]

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!!!

 

[psulion01]

bump for the afternoon crowd

 

[Norie]

I was going to answer this but was just stepping out the door.

The problem I'm having is that you seem to have a few contradictory statements in the post.

The main one being that you say you want to copy the entire worksheet, but you also say the data is filtered and a region is selected.

 

[psulion01]

I have a master data sheet and a report sheet. The report sheet contains a few formulas that filter through the master data in order to report on specific dates and regions.

I essentially want to run the calcs on my report sheet and then click a button that will capture all of the calculations and save them on a worksheet (with hard values, no formulas), for each region & date selected.

This would allow me to run the calcs on Region 1, Q1 2007, and then "save" the resulting report as a worksheet named 'Region1Q12007', or something to that effect.

Hope it makes a bit more sense. I'll try to post a sample of the report sheet, but the combo boxes don't seem to translate well on the board here.

Thx
Mike

 

[Norie]

Mike

Do you really need the comboxes?

 

[psulion01]

Mike

Do you really need the comboxes?

They're not necessary, but all they do is refer to a cell where their value is held, so removing them should not really change things. If the combo boxes must also get copied to the 'saved' versions of each region's report then no big deal, but if they can be removed automatically then that would be good also.

 

[psulion01]

Anyone?

 

[psulion01]

Still looking for some help if anyone is willing.

Thanks