Clear Pivot Table using VBA

Hi,

I have a Pivot table in my excel

I have a code to clear contents of the Active sheet if MsgBoxClick = "Yes". Along with clearing the contents of the Active sheet, I need Pivot table to be cleared which is in a different sheet.

Sheet4(PivotTable)

If MsgBoxClick = vbYes Then rgClear.ClearContents

I used below, but didn't work.

If MsgBoxClick = vbYes Then rgClear.ClearContents
If MsgBoxClick = vbYes Then Sheet4.PivotTables.ClearTable()

Can anyone please suggest me the way of deleting pivot table.

Thanks in advance.

Maby,

Please Login or Register  to view this content.

Hello,

I tried below, but did not work. May be I am missing something.


If MsgBoxClick = vbYes Then rgClear.ClearContents
If MsgBoxClick = vbYes Then Sheets("sheet4").PivotTables("PivotTable1").TableRange2.Clear

Hello,

I am trying to delete the Pivot table.

I have code to create pivot table, but it remains the same even when the data set changes.

so, I am looking for an option to delete the pivot table before I change the data.

Thanks in advance.

Please Login or Register  to view this content.

Hello,

Sorry for the delayed reply.

When I try the above code, I get "Unable to get the Pivot Tables property of the Worksheet class" error.
Also, to mention, the below statements work on 2 different sheets. Clear contents works on one sheet and Pivot table is in a different sheet that needs to get cleared when the message is yes. The first statement works but the second does not.


If MsgBoxClick = vbYes Then rgClear.ClearContents
If MsgBoxClick = vbYes Then ActiveSheet.PivotTables(1).TableRange2.Delete xlShiftUp

AciveSheet is the sheet on which code needs to be executed.

Thank you. I got it to work now.