Macro that will create a pivot table to an existing sheet

[papajups]

Hi All,


I am a beginner in Excel that is why I’m humbly asking for your help regarding below concern. I’m really hoping that you can assist me for it will really help me a lot in my job. Your assistance will be greatly appreciated. Thanks a lot in advance!

Is it possible to have a macro that will create a pivot table to an existing sheet?

I have two sheets named “Pending Asia” and “Pending Asia Pivot”. What I wish to happen is to create a pivot table using ‘Pending Asia’!B:P as reference of the table then insert the table to an existing sheet named “Pending Asia Pivot”, please put the table in column A8 of sheet “Pending Asia Pivot”. Add to report the field “Number of Days Pending” (This is column B in Pending Asia!) in Row Labels, Field name “Status Changed to” (Column M in Pending Asia!) in Column Labels and fieldname “Activity ID/Team Track” (Column F in Pending Asia!) to Values area.

Again, I am pleading for your help about this.

Thanks a lot and more power!


Regards,

papajups

 

[dispelthemyth]

There is a macro available that does this for you, the below should do the trick if you amend it to your data

Creating PivotTable Reports and Charts with VBA in Excel 2010

 

[dispelthemyth]

There is a macro going around that does this for you, the below should do the trick if you amend it to your data

Creating PivotTable Reports and Charts with VBA in Excel 2010

 

[papajups]

Hi dispelthemyth,


thank you very much for your prompt response as well as for the excel lesson you just shared. I was able to tweak the codes in the link you provided to me. Below are the codes:

Sub CreatePivot()

ActiveWorkbook.Sheets("Pending Asia").Select
Range("B:P").Select
Set objTable = ActiveSheet.PivotTableWizard

Set objField = objTable.PivotFields("Number of Days Pending")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("Status Changed to")
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("Activity ID/Team Track")
objField.Orientation = xlDataField
objField.NumberFormat = "0"

End Sub

It is working now but I would like the pivot table to be inserted in column A11 of sheetname "Pending Asia Pivot" if possible. Could you please help me with this?

Again, I really appreciate your help.

 

[papajups]

Hi All,

Happy New Year!

Can anyone help me regarding above request?

Your help will be highly appreciated.

Thanks a a lot in advance!


regards,

Juperth