[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Button to create a sheet

Posted on 2015-02-11
12
Medium Priority
?
53 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 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