Macro button to separate workbook as a file name referenced from cell.

I have a workbook with 10 tabs named for sales reps at my company, 5 tabs are scorecard reports and 5 tabs are revenue reports. The file also has a “home page” with formulas to check for errors.

I want to add a macro to the hope page that will separate the 2 tabs for each sales rep into a new workbook with a name derived from a cell.

I can record the action of separating the files, but I can’t bring the name along with it. I have about 20 files like this and it’s very time consuming to separate, save, and name the files. Any help would be great.

Here's an example of a file name I use:

Scorecard_Jones_Jun12

The rep's last name and the current month.

You could use something like;

Please Login or Register  to view this content.

Where Cells(1, 1) - e.g. Cell A1, contains the name of your file "Scorecard_jones_Jun12.xls".

If you post a sample workbook I could write something a lot cleaner (espeically if you're basing a lot of your code on "recorded" macros).

Here's an example. On the Rep1 tab I've added a macro for saving the workbook as the name in cell A1. Problem is, once I have separated Rep1 and Rev_Report_Rep1 tabs into a new workbook, the macro on Rep1 won't work. RepSheet_test.xlsm

Here you go Moonpie;

Just assign the sub "CallFunc" macro to any of the Rep# sheets you have setup and it will pull A1 + the sheetname to create the appropriate workbooks - There's probably a more efficient way to setup your rep workbook creating method, but this will accomplish what you specified nonetheless:

**Also, I wrote this as a separate "CallFunc" sub incase you wanted to explore a more robust method that could convert multiple reps at once, etc (e.g. pass this information multiple times through the sub in VBA).

Please Login or Register  to view this content.

Thanks, seems to work in the workbook containing all the reps, but once I separate the Rep1 using the recorded macro on the home page, any macros will not work on the new worksheet if I had created them on the original. Is there a way to get a macro to come along witht the new worksheet and be functional?

I need to have the Macro work once the sheet is separated. Goes like this. I click my macro for separating the sheets, then click macro on those sheets to save them in specific folder. Done. Please help