Solved

need to create new worksheet tabs via macro

Posted on 2014-07-31
8
256 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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