Solved

Button to create a sheet

Posted on 2015-02-11
12
45 Views
Last Modified: 2016-02-11
Hi,

I would like to add a button on a worksheet to create a data sheet when you click on it. The data sheet only need to have selected values I pick. The data on that sheet need to open in a new workbook.
0
Comment
Question by:Marcell999
  • 6
  • 5
12 Comments
 

Author Comment

by:Marcell999
ID: 40603270
The button needs to be connected to a pivot table
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40603313
Hi Marcell999,

I'm not sure i fully understand what you need.

Can you please send a template of that would be the excel file and also what would be the final expectation results?

This will be easier to help you.

I assume you want to create a VBA command button that will create a new workbook with a some fields transfer. Do you also need to save the bew file under a specific name and things like that?
0
 

Author Comment

by:Marcell999
ID: 40603387
Hi,

See the attached template. When I click on the button it needs to populate a new workbook with the data as display on sheet "Final Results"
0
 

Author Comment

by:Marcell999
ID: 40603393
Hi,

 See the attached template. When I click on the button it needs to populate a new workbook with the data as display on sheet "Final Results"
Tracker-1-.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40603520
With the bellow macro, you will have to select the Range cells you want to transfer to a new workbook and then, click on the command button.

Inside the command button put this below code:
    Dim NewBook As Workbook 
    Dim rng As Range 
    Set rng = Selection 
    Set NewBook = Workbooks.Add 
    With NewBook 
        .Title = "New file name" 
        .SaveAs Filename:="New file name.xls" 
    End With 
    rng.Copy Destination:=Range("A1") 

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40603556
Another way could be to transfer the full Pivot table by default to a new Workbook.

  Dim Pivot_trans As Boolean
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
    End With

Open in new window

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 11

Expert Comment

by:Wilder1626
ID: 40603608
The below macro would transfer the Pivot Table, and also save the file.

You can update the final file naming is you want.

Just need to adjust this part of the code:
sFileName = "Pivot table Result " & Date


  Dim Pivot_trans As Boolean
  Dim sPath As String, sFileName As String
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
    End With
    
    sPath = ThisWorkbook.Path & "\"
    sFileName = "Pivot table Result " & Date
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sPath & sFileName)
    ActiveWorkbook.Close True

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40603643
Pivot table has the option that when a data value total is double clicked it will generate a new sheet of data of the values that make up the value clicked. You can then just move/copy that new sheet to another workbook

Is this what you want?
0
 

Author Comment

by:Marcell999
ID: 40605211
To Wilder:

I like this code:

 Dim Pivot_trans As Boolean
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
    End With

But is there a way to only transfer the data and not the whole pivot table? Also is there a way to only transfer the values in the pivot? Meaning I only would like to transfer what I selected to filter in the pivot as data?
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 40605487
HI

You can try like this:
 Dim Pivot_trans As Boolean
  Dim sPath As String, sFileName As String
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
    End With
    
    sPath = ThisWorkbook.Path & "\"
    sFileName = "Pivot table Result " & Date
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sPath & sFileName)
    ActiveWorkbook.Sheets("Sheet2").Delete
    ActiveWorkbook.Close True

Open in new window

0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40605500
I'm glad i was able to help.

If you only want the value only, you can try like this:
  Dim Pivot_trans As Boolean
  Dim sPath As String, sFileName As String
    With Sheets.Add
        Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
        .Move
        With ActiveSheet.PivotTables(1)
            Pivot_trans = .ColumnGrand
            .ColumnGrand = True
            .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
            .ColumnGrand = Pivot_trans
            .Parent.Activate
        End With
    End With
    
    sPath = ThisWorkbook.Path & "\"
    sFileName = "Pivot table Result " & Date
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sPath & sFileName)
    ActiveWorkbook.Sheets("Sheet2").Delete
    
    ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
    
    ActiveWorkbook.Close True

Open in new window

0
 

Author Comment

by:Marcell999
ID: 40605514
Thanks for this
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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
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 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.

920 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

15 Experts available now in Live!

Get 1:1 Help Now