Copy Sheet (New) > Remove formulas > Save

**ExcelAteMyHomework** · 01-31-2012, 10:25 AM

Hello friends:

I have a rather large financial model that calculates commission payout for approximately 30 sales reps. Each rep has his/her own summary worksheet. After all calculations are made I normally save the entire workbook as another file name and remove all formulas completely. I then copy and move each rep summary sheet individually to it's own file and save it (without formulas).

I haven't messed with building a macro for this yet. Any recommendations or things I should keep in mind as I try to do that? Am I going to have to get into the VBA side of it or will I just have to go sheet by sheet and hit an assigned button for the macro to perform the task on said sheet?

Thank you

**StephenR** · 01-31-2012, 10:37 AM

Perhaps something like this?

Sub x()

Dim ws As Worksheet

ThisWorkbook.SaveAs "Whatever.xls"

For Each ws In Worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
    ws.Copy
    ActiveWorkbook.Close True, "H:\" & ws.Name & ".xls"
Next ws

End Sub

**ExcelAteMyHomework** · 01-31-2012, 11:05 AM

Originally Posted by StephenR

Perhaps something like this?

Sub x()

Dim ws As Worksheet

ThisWorkbook.SaveAs "Whatever.xls"

For Each ws In Worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
    ws.Copy
    ActiveWorkbook.Close True, "H:\" & ws.Name & ".xls"
Next ws

End Sub

Excellent. I will try that. Thank you Stephen.

**ExcelAteMyHomework** · 02-09-2012, 04:48 PM

So let's assume the active workbook is the file using the macro.

All I want to do is Copy the active worksheet to it's own workbook, remove all formulas, save as worksheet name, move to next worksheet.

What does that code look like?

Thanks!

**StephenR** · 02-10-2012, 05:40 AM

That's what that code does. What is it not doing that you want it to do?

**ExcelAteMyHomework** · 02-14-2012, 10:24 AM

Maybe I am entering the code incorrectly. I suck at VBA.

**ExcelAteMyHomework** · 03-20-2012, 04:17 PM

Stephen:

I got this working, however I would like to update my save filepath to a specific folder on the specified drive. For example, "H:\Calculations\Commissions\Test". However, when I update my code it actually saves all the files to "H:\Calculations\Commissions" and adds the word "Test" to the beginning of each filename.

Your thoughts?

**ExcelAteMyHomework** · 03-20-2012, 04:24 PM

Nevermind I got this to work.

**StephenR** · 03-21-2012, 08:33 AM

You asked by PM "is it possible to add on to the code and have a worksheet attach itself to an Outlook email?". Please keep questions on the forum.

I have not done much of this, but you might like to check this site: http://www.rondebruin.nl/tips.htm which has quite a lot on emailing.