need to create new worksheet tabs via macro

Hi,

I need to create a macro where each time I hit a button it copies the contents of my existing worksheet tab into a new worksheet tab.  See my attached spreadsheet for the example.  Specifically, from the "compare plans" worksheet tab, I need columns A through H copied as values (rather than formulas) into a new tab called whatever is in cell F14 of "compare plans".  (The other tabs should remain intact, as is, and not be changed in any way.)  For example, in my spreadsheet, I would the tab "compare plans" and manually adjust column D (which runs financial formulas....actually any cell in yellow can be adjusted manually to run formulas) to observe the effects of a new financial portfolio.   Once I am happy with my changes, I click the macro button and columns A - H are copied into a new worksheet titled "121,500".   This would save a record of that financial portfolio option.

Thanks in advance!
sample.xlsx
Cam RabenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Insert this code in a module.
The code makes a copy of the entire worksheet (to keep formatting) to a new sheet, after the last sheet in the workbook.
Then change all cells to values, and name the sheet the value in F14.

Sub CopySheetToRecord()
    Sheets("COMPARE PLANS").Copy After:=Sheets(Worksheets.Count)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.Name = WorksheetFunction.Text(Range("F14"), "#,###")
End Sub

Open in new window

0
Cam RabenAuthor Commented:
I'm running excel 2010.  how do i insert this macro in a module?
0
Cam RabenAuthor Commented:
Found it.  Loaded and ran the macro and got the error:
run time error 1004.  application-defined or object-defined error.

Please advise.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Cam RabenAuthor Commented:
Although it gives the error noted above it still does the following:
1.  creates 2 new tabs, "compare plans (2)" and "compare plans (3)".
2.  populates them with cells that no longer function properly.
3.  the cells are copied as formula, not values, and the formula do not work.

??
0
Ejgil HedegaardCommented:
You ran the program more than once with the same value in F14, and a sheet can not be given a name that already exists, giving the run time error.
The 2 tabs are created on the extra runs, but not named properly.

For point 2 and 3 I don't understand what you mean.
The sheet is copied as it is, a duplicate is made with the same functionality and formatting as the original sheet.
Then the formulas are changed to values.


Use this to avoid the run time error, program stops if a sheet with the name (F14 in Compare plans) already exists.

Option Explicit

Sub CopySheetToRecord()
    Dim ws As Worksheet, shName As String, i As Integer
    
    shName = WorksheetFunction.Text(Sheets("COMPARE PLANS").Range("F14"), "#,###")
    i = 0
    For Each ws In Worksheets
        If ws.Name = shName Then
            i = 1
        End If
    Next ws
    If i = 1 Then
        MsgBox "Sheet " + shName + " exists, copy not performed, program stop", vbCritical
        End
    Else
        Sheets("COMPARE PLANS").Copy After:=Sheets(Worksheets.Count)
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
        ActiveSheet.Name = shName
    End If
End Sub

Open in new window

0
Cam RabenAuthor Commented:
Great - How do I run the macro from within that sheet?  (Your new version works as you said, but when I alter the sheet it does not automatically create the new tab.)  I'd like to be able to press a button that is on that sheet and have the macro run.  Is that possible?
0
Ejgil HedegaardCommented:
Yes, that is possible, see file.
CopySheetToRecord.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cam RabenAuthor Commented:
Beautifully done Ejgil - thanks very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.