Need macro to copy data to new sheet & name worksheet

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
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!!!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 25, 2002
Messages
127
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Mike

Do you really need the comboxes?
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top