Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

need to create new worksheet tabs via macro

Posted on 2014-07-31
8
Medium Priority
?
261 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 24

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 24

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 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 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