Creating Pivot chart from Macro

Naveen Prakash

New Member
Joined
Feb 4, 2019
Messages
1
I have written a macro to create a Pivot chart in Excel in one Workbook. If I am running the macro from the same workbook it is adding the pivot chart proper without any error. But if I am running the macro from another workbook, I am getting the Application defined or Object defined error in following line.

Code:
[COLOR=#242729][FONT=Consolas]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:=DSheet.Cells(Table1_Start_Line, Column_Line), TableName:="HSDPivotTable", DefaultVersion:=xlPivotTableVersion15[/FONT][/COLOR]


My Complete Code is below:

Code:
Sub InsertPivotTable()'Macro By ExcelChamps


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PTable1 As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long


Dim Table1_Row_line As Integer
Dim Table1_Column_line As Integer
Dim Table2_Row_line As Integer
Dim Table2_Column_line As Integer


Dim Chart1_Row_Line As Integer
Dim Chart1_Column_Line As Integer
Dim Chart2_Row_Line As Integer
Dim Chart2_Column_Line As Integer


'Insert a New Blank Worksheet
On Error Resume Next
Set DSheet = Worksheets("Sheet1")
Worksheets("Sheet1").Activate


'Define Data Range
LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


Table1_Start_Line = 2
Table1_End_Line = Table1_Start_Line + LastRow
Column_Line = LastCol + 2


Table2_Start_Line = Table1_End_Line + 2
Table2_End_Line = Table2_Start_Line + LastRow




ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:=DSheet.Cells(Table1_Start_Line, Column_Line), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion15
Sheets("Sheet1").Select
DSheet.Cells(Table1_Start_Line, Column_Line).Select


With ActiveSheet.PivotTables("PivotTable").PivotFields("WW")
.Orientation = xlRowField
.Position = 1
End With


ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables("PivotTable").PivotFields("Actual"), "Actual ", xlSum
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables("PivotTable").PivotFields("Actual(cumulative)"), "Actual (cumulative) ", xlSum


ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$Z$" & Table1_Start_Line & ":$AB$" & Table1_End_Line)
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "Chart1"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart1"
    
ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable").PivotCache.CreatePivotTable TableDestination:=DSheet.Cells(Table2_Start_Line, Column_Line), TableName:="IncomingVsCompletion", DefaultVersion:=xlPivotTableVersion15
Sheets("Sheet1").Select
Cells(Table2_Start_Line, Column_Line).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("IncomingVsCompletion").PivotFields("WW")
.Orientation = xlRowField
.Position = 1
End With


ActiveSheet.PivotTables("IncomingVsCompletion").AddDataField ActiveSheet.PivotTables("IncomingVsCompletion").PivotFields("Plan"), "Plan ", xlSum
ActiveSheet.PivotTables("IncomingVsCompletion").AddDataField ActiveSheet.PivotTables("IncomingVsCompletion").PivotFields("Plan (cumulative)"), "Plan (cumulative) ", xlSum


ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$Z$" & Table2_Start_Line & ":$AB$" & Table2_End_Line)
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "Chart2"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart2"
    
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi Naveen,

Running macros from a workbook/worksheet that is different from the original activeworkbook/activesheet that you original made the macro for can be tricky.

When you write
Code:
[COLOR=#333333]Set DSheet = Worksheets("Sheet1")[/COLOR]

This actually translates to
Code:
[COLOR=#333333]Set DSheet = ACTIVEWORKBOOK.Worksheets("Sheet1")[/COLOR]
So when you ran the code from the original workbook where the data was, everything was ok. When you run the code from a different workbook, DSheet is now probably referring to the workbook you are running the code from, which does not have the data, which is probably an error. I am pretty certain that this is one issue with the code above, but I suspect there might be more.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top