Excel 2003 - deleting connection of pivottable based on external data source

Hello Experts

I have a pivottable based on external data source (Excel 2003).
Can I delete link to this data source from VBA, so that the pivottable can no longer be refreshed?
something like: Sheets(1).QueryTables(1).Delete

thank you very much,
Jarek

LVL 1
ja-rekAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Select the entire Pivot Table, including the Page fields and do a Copy > Paste Speceial > Values.

This will delete the link and leave a replica of the original pivot but without any of the pivot functionality.

Thanks
Rob H
0
 
jppintoCommented:
ActiveSheet.PivotTables(1).SourceData = ""
0
 
ja-rekAuthor Commented:
jppinto, it doesn't work in my Excel 2003.
0
 
jkasavanCommented:
I am not sure if this works in Excel 2003.

In Excel 2002 assume the source is a range called Data.

Manual steps

Insert -> Name -> Define -> select the name of the range to delete -> select Delete.

Recorded VBA MAcro

Sub NoMorePivot()

      ActiveWorkbook.Names("Data").Delete

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.