+ Reply to Thread
Results 1 to 9 of 9

Pivot table - break connection to data source

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    246

    Pivot table - break connection to data source

    Does anyone know if it's possible to break the connection between a pivot table and the data source whilst still maintaining the data in the table? I could try copy/paste special/paste values but thought there migth be a 'proper' way to preserve the data.

    Thanks
    Rob

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Pivot table - break connection to data source

    I just copy...paste special...values, works fine for me.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Pivot table - break connection to data source

    Same here for me

  4. #4
    Registered User
    Join Date
    08-17-2011
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Pivot table - break connection to data source

    Is there any way to break the connection and still be able to manipulate the pivot table? When I remove the data connection, the pivot table no longer functions like a pivot table (i.e. no field list, no filtering, no dragging/dropping of fields, etc.). I thought that the pivot table data (the pivot cache) would still be there after removing the connection and allow full pivot table functionality but that is not the way it is working. The issue here is that I have a workbook with a pivot table that can be refreshed at will after its weekly source data is updated. There is occasion where someone wants to save the data 'as is' so we just save the workbook with a different user-specified name. After this I want to remove the data connection so the user won't accidentily refresh it with new data. But then I would like for them to be able to manipulate the old data with the pivot table.

  5. #5
    Registered User
    Join Date
    08-23-2011
    Location
    Exeter, England
    MS-Off Ver
    Excel 2003 (Work), 2007 (Home)
    Posts
    18

    Re: Pivot table - break connection to data source

    Can you copy the source data too, and put it in a hidden sheet?

  6. #6
    Registered User
    Join Date
    08-17-2011
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Pivot table - break connection to data source

    "I" could, but it is not really me doing it. I have set up several workbooks for several users of varying talent to get fresh data each week. They are the ones that might want to save a week. It would be nice if they could just save to a different name, remove the connection, and go on as if the connection were still there. I still think the pivot cache is there and don't understand why it won't work like a normal pivot table.

  7. #7
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Pivot table - break connection to data source

    Start your own thread please per the forum rules.

    Dom

  8. #8
    Registered User
    Join Date
    11-29-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Pivot table - break connection to data source

    I'd like to copy a pivot table into a new sheet for someone but don't want them to be able to drill down into the data. The paste as values works, but is there a way to also maintain the formatting when pasting as values? Thank you.

  9. #9
    Registered User
    Join Date
    08-07-2013
    Location
    lodon
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Pivot table - break connection to data source

    Quote Originally Posted by HGBrunson View Post
    Is there any way to break the connection and still be able to manipulate the pivot table? When I remove the data connection, the pivot table no longer functions like a pivot table (i.e. no field list, no filtering, no dragging/dropping of fields, etc.). I thought that the pivot table data (the pivot cache) would still be there after removing the connection and allow full pivot table functionality but that is not the way it is working. The issue here is that I have a workbook with a pivot table that can be refreshed at will after its weekly source data is updated. There is occasion where someone wants to save the data 'as is' so we just save the workbook with a different user-specified name. After this I want to remove the data connection so the user won't accidentily refresh it with new data. But then I would like for them to be able to manipulate the old data with the pivot table.
    i'm trying to do the same thing...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1