Marcell999
asked on
Button to create a sheet
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.
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.
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?
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?
ASKER
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"
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"
ASKER
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
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
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:
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")
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
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
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
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?
Is this what you want?
ASKER
To Wilder:
I like this code:
Dim Pivot_trans As Boolean
With Sheets.Add
Sheets("Pivot (3)").PivotTables(1).Table Range2.Cop y .Range("A1")
.Move
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells (xlCellTyp eLastCell) .ShowDetai l = True
.ChangePivotCache ActiveWorkbook.PivotCaches .Create(xl Database, 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?
I like this code:
Dim Pivot_trans As Boolean
With Sheets.Add
Sheets("Pivot (3)").PivotTables(1).Table
.Move
With ActiveSheet.PivotTables(1)
Pivot_trans = .ColumnGrand
.ColumnGrand = True
.Parent.Cells.SpecialCells
.ChangePivotCache ActiveWorkbook.PivotCaches
.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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad i was able to help.
If you only want the value only, you can try like this:
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
ASKER
Thanks for this
ASKER