Automatically Delete Pivot Table with Macro?

I have a macro where I create a pivot table on a new sheet, then want to just take the text values of it without the drop down list. What I end up doing is selecting the current region then copying and pasting it onto a new sheet, only pasting the values. At the end of the Macro I want to delete the sheet with the Pivot Table on it that I'm not using. Even with a Macro, it still prompts me and makes me click the Delete Button. It says "Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete".

Is there any way around this so I don't have to click it? I'm making this for someone, and it does not make sense for them to hit the delete button everytime. Either that or if I could make the pivot table and somehow automatically convert it to text values. I don't want a pivot table to be seen.

Any thoughts or ideas would be great!

~J

To prevent the warning messages, set DisplayAlerts to False. Delete the
sheet, then set DisplayAlerts back to True, as follows:

Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True

If you would rather convert the pivottable to text, that's easy, too.
1. Select a cell in the pivottable.
2. Select the CurrentRegion.
3. Copy & paste in place as values.

Hope this helps,

Hutch

"nbaj2k" wrote:

>
> I have a macro where I create a pivot table on a new sheet, then want to
> just take the text values of it without the drop down list. What I end
> up doing is selecting the current region then copying and pasting it
> onto a new sheet, only pasting the values. At the end of the Macro I
> want to delete the sheet with the Pivot Table on it that I'm not using.
> Even with a Macro, it still prompts me and makes me click the Delete
> Button. It says "Data may exist in the sheet(s) selected for deletion.
> To permanently delete the data, press Delete".
>
> Is there any way around this so I don't have to click it? I'm making
> this for someone, and it does not make sense for them to hit the delete
> button everytime. Either that or if I could make the pivot table and
> somehow automatically convert it to text values. I don't want a pivot
> table to be seen.
>
> Any thoughts or ideas would be great!
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=563024
>
>