Pivot Chart Macro Question

[tesla12345]

Hey Guys!

Im trying to do a Macro to post a chart from a pivot table ( i used record macro first to understand what was being done). One problem through i cannot get the row variable in the pivot table to be correct, (highlighted in black) I think it has something to do with the dynamic range.... thanks for the help guys

Sub Macro5()
'
' Macro5 Macro
'


'
Range("A2:AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range("A2:AD2206").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"TotalGama c Sku!R2C1:R2206C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="", TableName:="PivotTable4" _
, DefaultVersion:=xlPivotTableVersion14

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered




ActiveChart.SetSourceData Source:=Range("Sheet6!$A$1:$C$18")
ActiveSheet.Shapes("Chart 1").IncrementLeft 192
ActiveSheet.Shapes("Chart 1").IncrementTop 15
With ActiveSheet.PivotTables("PivotTable4").PivotFields("DC")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Categorização Margem Son")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Categorização Margem Son"), _
"Count of Categorização Margem Son", xlCount
End Sub

[LokeshKumar]

Hi,

Your code should be pasted under code tag.However please see below codes.

Please Login or Register  to view this content.

Here you can do one thing.. use the following code......

Please Login or Register  to view this content.

If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

[tesla12345]

Hey!

GIve error 438 :/

[LokeshKumar]

could you upload the workbook with the code........

[tesla12345]

thank you for the promt replys

When you talk about the workbook, you mean the actual excel file? the complete code is in the first post, please let le me know what you need

[LokeshKumar]

This code will not work until I have the data.....

I do not have the data in new workbook.....

Please Login or Register  to view this content.

[tesla12345]

Hey no problem!

One thing though the forum doesnt let me uplaod files :/

[LokeshKumar]

send me
[email protected]

[tesla12345]

http://we.tl/NsKZ58yxay

[tesla12345]

Thank you so much for the help

Wanted I wanted to do is to put DC in the axis elgend and Cat Margem SOn in the Legend & value section

I had to delete the other informatio as the analysis is of sensative nature but dont take me wrong

[tesla12345]

Here is the file!

[LokeshKumar]

I understand it ........
But I really do not able to get on what data you have recorded above mentioned macro.....
anyways try this......not sure..

Please Login or Register  to view this content.

[tesla12345]

so strange, the code came from record macro... still not working but thank you for the help

[LokeshKumar]

Hey
Your issue with switching the sheets....since you have switch the sheet so macro is not constant......

[tesla12345]

the sheet will always change no, if I create multipel pivot tables?