Solved

need to create new worksheet tabs via macro

Posted on 2014-07-31
8
253 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

758 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

23 Experts available now in Live!

Get 1:1 Help Now