Hey guys,
Im new with VBA so do help me out.
I am trying to create a pivot table (part of a larger set of codes).
There are 3 sheets:
Summary, Details & Sheet3(Button is located in sheet 3)
I want to pivot the table in "Summary" based on the data in "Details"
But I keep getting the error message. Application defined or object defined error.
'PivotTable working
Dim dataSource As Range
Dim datadest As Range
Dim pivotinfo As PivotTable
Dim Table As Range
Dim wkMain As Worksheet
'Setting up sourcedata to be auto select in details tab
Set wkMain = Worksheets("Details")
Range("A3:W3").Select
Set Table = Range(Selection, Selection.End(xlDown))
Set dataSource = Worksheets("Details").Range("Table")
Set datadest = Worksheets("Summary").Range("A2")
'To generate pivottable
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, Sourcedata:=dataSource, _
TableDestination:=datadest, TableName:="Pivotinfo"
'Adding fields to pivottable
With Worksheets("Summary").PivotTables("Pivotinfo")
.PivotFields("REPORTING_PERIOD").Orientation = xlRowField
.PivotFields("ACCOUNT_NO").Orientation = xlRowField
.PivotFields("PRODUCT_OVERVIEW").Orientation = xlColumnField
.PivotFields("AMOUNT_EUR").Orientation = xlDataField
End With
End Sub
Im new with VBA so do help me out.
I am trying to create a pivot table (part of a larger set of codes).
There are 3 sheets:
Summary, Details & Sheet3(Button is located in sheet 3)
I want to pivot the table in "Summary" based on the data in "Details"
But I keep getting the error message. Application defined or object defined error.
'PivotTable working
Dim dataSource As Range
Dim datadest As Range
Dim pivotinfo As PivotTable
Dim Table As Range
Dim wkMain As Worksheet
'Setting up sourcedata to be auto select in details tab
Set wkMain = Worksheets("Details")
Range("A3:W3").Select
Set Table = Range(Selection, Selection.End(xlDown))
Set dataSource = Worksheets("Details").Range("Table")
Set datadest = Worksheets("Summary").Range("A2")
'To generate pivottable
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, Sourcedata:=dataSource, _
TableDestination:=datadest, TableName:="Pivotinfo"
'Adding fields to pivottable
With Worksheets("Summary").PivotTables("Pivotinfo")
.PivotFields("REPORTING_PERIOD").Orientation = xlRowField
.PivotFields("ACCOUNT_NO").Orientation = xlRowField
.PivotFields("PRODUCT_OVERVIEW").Orientation = xlColumnField
.PivotFields("AMOUNT_EUR").Orientation = xlDataField
End With
End Sub