Need help in creating a Pivot table in excel sheet using VBA

[bhandarx]

Hi,

I created a pivot table using the vba code shown below. I needed to achieve few other changes with respect to this and need your help on the same. I am using Excel 2010.

1. The code is creating the pivot table in new sheets (like Sheet1, Sheet2, Sheet3 etc) on re-executing the code every time. I need to know how to ensure that the pivot table is created in a new sheet with a name of my choice? i.e. Whenever I run the macro the pivot table should be created in a new sheet named as "MyChoice"

2. If the macro is being run for the second time, then the code should be able to delete the existing pivot table in the sheet say "MyChoice" and create the new Pivot Table in the same sheet "MyChoice"

<Code>

Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("RAWDATA").Select
Range("A1").Select

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = ActiveWorkbook.Sheets("RAWDATA").PivotTableWizard
objTable.Name = "Consumption Pivot"

' Specify row and column fields.
Set objField = objTable.PivotFields("PART DESCRIPTION")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("DESCRIPTION")
objField.Orientation = xlRowField

'objField.Orientation = xlColumnField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("QUANTITY")
objField.Orientation = xlDataField
objField.Function = xlSum


' Specify a page field.
Set objField = objTable.PivotFields("FAMILY")
objField.Orientation = xlPageField

Set objField = objTable.PivotFields("BUSINESS AREA")
objField.Orientation = xlPageField

</code>


Could you please help me to achieve this?


Best Regards,
bhandarx