?
Solved

Button to create a sheet

Posted on 2015-02-11
12
Medium Priority
?
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

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!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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