Solved

Button to create a sheet

Posted on 2015-02-11
12
48 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

680 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