Create pivot table in existing worksheet

[Lalali]

Hi All,
I'm a total newbie , so please apologies for my poor language
I need a macro that creates a pivot table in the existing worksheet (named C-grades).
Range is O11:T500.
Location pivot: 'C-grades'!$X$11
Fields to add to report: "material" (in rows) and "C-grade Quantity" (sum of C-grades Quantity)

After that, I would like to V-lookup some cells (from S12 down) of another sheet (named "Discrepancy Report Material") of the same workbook, with the sum of the C-grades quantity in the pivot table

I tried to read some topic in this forum about it, but I don't clearly understand since i'm very at the beginning

I hope you can help me
Thanks!

 

[Kamolga]

To create the pivot, you should record a macro while you create it, so everytime you launch this macro, it creates the same pivot.

The question is why do need to create a pivot several times, isn't it easier to refresh it?

For the "V lookup", I would record a copy-paste at the right place (a table) and delete the duplicates, then the formula (vlookupup) in the table would get the values. I would also use tables for initial data (to get the pivot), the advantage being that the range (amount of lines) can vary in the future, the macro would always take the full column of the table.

 

[Lalali]

Yes I tried to record a macro but it didn't work. From what I read I first need to create a pivot cache?

I need to create a macro because the pivot is just a small part of a long macro.

thanks!

 

[Lalali]

I tried again to record the pivot table. But i got Run-time error 5 invalid procedure call or argument
how can I fix it?


Range("O11:T500").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C-grades!R11C15:R500C20", Version:=6).CreatePivotTable TableDestination:= _
"C-grades!R11C24", TableName:="PivotTable1", DefaultVersion:=6

 

[Kamolga]

Yes I tried to record a macro but it didn't work. From what I read I first need to create a pivot cache?

You have to delete the pivot before launching the macro again...and you might want to use a dynamic name. I would rather set the pivot once manually and use

thisworkbook.refreshall​

than deleting and resetting the pivot everytime.

[LEFT][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']Sheets("C-Grades").Select[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']   
Range("O11:T500").Select[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']      
 "C-Grades!R11C15:R500C20", Version:=6).CreatePivotTableTableDestination:= _[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit'] 
"C-Grades!R11C24", TableName:="PivotTable1",DefaultVersion:=6[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']   
Sheets("C-Grades").Select[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']   
Cells(11, 24).Select[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']    
WithActiveSheet.PivotTables("PivotTable1").PivotFields("material")[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']       
.Orientation = xlRowField[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']      
 .Position = 1[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']    
EndWith[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']   
ActiveSheet.PivotTables("PivotTable1").AddDataFieldActiveSheet.PivotTables( _[/FONT][/COLOR][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit'] 
      "PivotTable1").PivotFields("C-Grades Quantity"),"Count of C-Grades Quantity", _[/FONT][/COLOR][/FONT][/COLOR][LEFT][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']xlCount[/FONT][/COLOR][/FONT][/COLOR][/LEFT]
[COLOR=#222222][FONT=Verdana][LEFT][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']    WithActiveSheet.PivotTables("PivotTable1").PivotFields( _[/FONT][/COLOR][LEFT][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']       "Count of C-Grades Quantity")[/FONT][/COLOR][LEFT][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']       .Caption = "Sum of C-Grades Quantity"[/FONT][/COLOR][LEFT][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']       .Function = xlSum[/FONT][/COLOR][LEFT][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][COLOR=#222222][FONT=Verdana][COLOR=#0000ff][FONT='inherit']    EndWith[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

 

[Lalali]

I fixed myself. I put a hyphen in here
"'C-grades'!R11C24" and it solved the issue