Solved

need to create new worksheet tabs via macro

Posted on 2014-07-31
8
254 Views
Last Modified: 2014-07-31
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
0
Comment
Question by:Cam Raben
  • 5
  • 3
8 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40232272
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
 

Author Comment

by:Cam Raben
ID: 40232345
I'm running excel 2010.  how do i insert this macro in a module?
0
 

Author Comment

by:Cam Raben
ID: 40232438
Found it.  Loaded and ran the macro and got the error:
run time error 1004.  application-defined or object-defined error.

Please advise.
0
 

Author Comment

by:Cam Raben
ID: 40232457
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40232781
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
 

Author Comment

by:Cam Raben
ID: 40232868
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
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40232893
Yes, that is possible, see file.
CopySheetToRecord.xlsm
0
 

Author Closing Comment

by:Cam Raben
ID: 40233318
Beautifully done Ejgil - thanks very much!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now