Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: delete a pivot table (but not the data or format)

  1. #1
    Board Regular
    Join Date
    Apr 2004
    Location
    New York City!
    Posts
    348
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default delete a pivot table (but not the data or format)

    i want to retain the data and format, but get rid of the pivot capacity before sharing the spreadsheet. right now if a copy/paste special, i can get the data, but not the formats, any suggestions?
    OfficeXP on WinXP

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete a pivot table (but not the data or format)

    Copy - Paste Special Values, followed immediately by Paste Special Formats.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    Board Regular
    Join Date
    Apr 2004
    Location
    New York City!
    Posts
    348
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: delete a pivot table (but not the data or format)

    Jon, in 2007 this does not work, but what you can do is get the address property (parse out if it's an array) and copy just the range of the larger portion of the pivot.

    tmp = pv.address
    if instr(1,tmp,",") then
    tmp = split(tmp,",")(1)
    range(tmp).copy

    then do the paste special as described
    OfficeXP on WinXP

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete a pivot table (but not the data or format)

    I recently was told how to make this work in 2007. Copy part of the pivot table, all but one row for example, then paste special values plus paste special formats. Copy the remainder of the pivot table, and repeat the two paste special operations.

    This is nothing more than taking your solution and doing it twice to get the entire table.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •