Solved

Button to create a sheet

Posted on 2015-02-11
12
44 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
Comment Utility
The button needs to be connected to a pivot table
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for this
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now