Pivot Table VBA

[ycong]

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

 

[Domenic]

Try replacing...

[COLOR=#333333]Set dataSource = Worksheets("Details").Range("Table")[/COLOR]

with

[COLOR=#333333]Set dataSource = Worksheets("Details").Range([/COLOR][COLOR=#ff0000]Table.Address[/COLOR][COLOR=#333333])[/COLOR]

However, your code could be re-written as follows...

[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test2()


    [COLOR=darkblue]Dim[/COLOR] wksSource [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] wksDest [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rngSource [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rngDest [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wksSource = Worksheets("Details")
    
    [COLOR=darkblue]Set[/COLOR] wksDest = Worksheets("Summary")
    
    [COLOR=darkblue]With[/COLOR] wksSource
        LastRow = .Range("A3").End(xlDown).Row
        LastCol = .Range("A3").End(xlToRight).Column
        [COLOR=darkblue]Set[/COLOR] rngSource = .Range("A3", .Cells(LastRow, LastCol))
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rngDest = wksDest.Range("A2")
    
    ActiveSheet.PivotTableWizard _
        SourceType:=xlDatabase, _
        SourceData:=rngSource, _
        TableDestination:=rngDest, _
        TableName:="Pivotinfo"
        
    [COLOR=darkblue]With[/COLOR] wksDest.PivotTables("Pivotinfo")
        .PivotFields("REPORTING_PERIOD").Orientation = xlRowField
        .PivotFields("ACCOUNT_NO").Orientation = xlRowField
        .PivotFields("PRODUCT_OVERVIEW").Orientation = xlColumnField
        .PivotFields("AMOUNT_EUR").Orientation = xlDataField
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

 

[ycong]

thank you for ur help!

 

[Domenic]

You're welcome!