Hi,
I made a code that creates a new workbook, moves the data to a new tab in the new workbook, as well as creates a PivotTable and chart from the raw data.
The code works well if ran directly as a macro.
However, I made a userform to run three different versions of the code depending on which button you push.
If I run the code by selecting a button on the userform, it creates everything perfectly. Yet, if I click off of the chart (to the pivot table or the new raw data tab) then click back to the chart tab,
it moves the chart back to the original workbook...
Something must be wrong with my UserForm since it works fine when I run the macro directly, but I have no idea what is wrong. Please assist.
Here is the workbook as well as the code:
Raw Data.xlsm
My Chart code for one of my buttons is:
Private Sub CommandButton1_Click()
Call PivotTable_1
UserForm1.Hide
End Sub
My
Module code is:
Sub Load_PivotSheet_Options()
Load UserForm1
UserForm1.Show
End Sub
Sub PivotTable_1()
Dim PTCache As PivotCache
Dim PT As PivotTable
'select current region
ActiveSheet.Activate
ActiveCell.CurrentRegion.Copy
'Make new workbook
Workbooks.Add
Application.ScreenUpdating = False
ActiveCell.CurrentRegion.PasteSpecial
Sheets("Sheet1").Name = "Raw_Data"
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
'Add new Worksheet
Worksheets.Add
ActiveSheet.Name = "Financials"
ActiveWindow.DisplayGridlines = False
'Create the Pivot Table from the Cache
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=Range("A1"), _
TableName:="BudgetPivot")
With PT
'Add Fields
'xlPageField is for filter
.PivotFields("Account").Orientation = xlPageField
.PivotFields("Business Unit").Orientation = xlRowField
.PivotFields("Scenario").Orientation = xlRowField
.PivotFields("Jan").Orientation = xlDataField
.PivotFields("Feb").Orientation = xlDataField
.PivotFields("Mar").Orientation = xlDataField
.PivotFields("Apr").Orientation = xlDataField
.PivotFields("May").Orientation = xlDataField
.PivotFields("Jun").Orientation = xlDataField
.PivotFields("Year").Orientation = xlRowField
'Specify a number format
.DataBodyRange.NumberFormat = "0,000"
'Apply a Style
.TableStyle2 = "PivotStyleMedium3"
'Change the captions
.PivotFields("Values").Caption = " Budget & Actual"
End With
ActiveSheet.PivotTables("BudgetPivot").PivotSelect "", xlDataAndLabel, True
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Financials_Chart"
End Sub
Bookmarks