Can Pivot Table Data be "Disconnected"

[Bob_R]

All,

SUMMARY
Can only the data be copied from each tab that is linked to a pivot Table? I need to send each department a report. And I do not want then to be able to click in the Filters section to see other departments data.

DETAILS
I was able to use the Pivot Table Tools > Analyze > Options > Show Report Filter Pages functionality to generate separate tabs/ worksheets for each department. And found VBA on the web that then copies each tab /worksheet and creates a separate workbook for each. So far, so good.

ISSUE = each department's individual worksheet still has the underlying data "attached" to it. So the department could click in the filter and select other departments data.

Thanks,
Bob

 

[AlanSidman]

How about saving the individual worksheets to be sent out as PDF files. Then no one can make any changes nor see other departments.

 

[p45cal]

I'm not sure of my facts here, for you to experiment.
If the pivot table from which you Show Report Filter Pages has in its options the Save source data with file checkbox unchecked, I feel there's a good chance the resulting workbooks' pivot tables will retain that setting.



I think the pivot table might still try to connect to the original file, but if that file isn't available to those that use the file you send to them then they shouldn't be able to see the other data? Test this!

With luck, not only should other data be unavailable to them but the files should be smaller.

If you're using vba, you might get another layer of protection by disabling the pivot table refresh with code akin to:
Worksheets(1).PivotTables(1).PivotCache.EnbleRefresh = False

Otherwise, copy the pivot tables data and paste back as values only, but then you'll lose all pivot functionality.

 

[Bob_R]

All,

Thanks for responses.

1. PDF Option: Not sure how to change the VBA to copy as PDF versus Excel workbook. Presumption is in this line of VBA
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"

2. Pivot table Options > Data > Save Sources Data With File
I unchecked that for each worksheet before running the VBA. However, the new separate worksheets still are "connected" to the data. I do get the Security warning with the Enable Content button.
Is there a way to "not" enable the content?

I also found an option of using Paste Special > Values, Paste Special > Formats and Paste Special > Column Widths.
But when attempting to write a Macro, the Ctrl - A to select all cells did not select the report filter. I wanted to use a Macro and then run it for each of the 30-35 new individual department workbooks.

Thanks,
Bob

 

[p45cal]

However, the new separate worksheets still are "connected" to the data.

Do the end users have 'sight' of the data source? If not then you may be OK. On your system you're very likely still to have the source data available. Test on another machine (an end user's preferably).

Is there a way to "not" enable the content?

Ignore the message. I don't think it'll help though.

But when attempting to write a Macro, the Ctrl - A to select all cells did not select the report filter.

Try using code akin to:

With ActiveSheet.PivotTables(1).TableRange2
  x = .Value
  .ClearContents
  .Value = x
End With

 

[Bob_R]

Tested - "Do the end users have 'sight' of the data source? If not then you may be OK. On your system you're very likely still to have the source data available. Test on another machine (an end user's preferably). "

The Excel workbooks are on my local drive.

Unfortunately when I tested by sending to an end user they were to able to use the pivot table filter to recall other departments data.

My understanding is the pivot table data cache is somehow within the workbook. As I had already tried to delete the data tab before running the VBA to generate separate department workbooks.

I will probably also research if there is VBA that can be used if the data is just in a "normal" table. And the VBA would create a seprate workbook when the department name in column A changed?

 

[p45cal]

Unfortunately when I tested by sending to an end user they were to able to use the pivot table filter to recall other departments data.

Doing some of my own testing on just my machine, I get this when I try to change the filter:


If I refresh, I can see more data, but if the data source file is unreachable (I renamed it) I get:


(MS365).

I also noted that I lost a lot of the pivot functionality, so maybe if you do too, the solution is to replace the pivot with its values.

 

[p45cal]

I unchecked that for each worksheet before running the VBA.

I would do it for the main pivot before you Show Report Filter Pages.

tried to delete the data tab before running the VBA to generate separate department workbooks

Could you post the code you're using?

 

[Bob_R]

1. "I would do it for the main pivot before you Show Report Filter Pages. "
Tested. It will now no longer allow me to use the Show Report Filter Pages functionality.

2. VBA CODE
See below:
>>> use code - tags <<<

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[p45cal]

It will now no longer allow me to use the Show Report Filter Pages functionality.

Is the source data for the pivot not in the same workbook as the pivot?

Last resort:

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    With ActiveSheet.PivotTables(1).TableRange2
          x = .Value
          .ClearContents
          .Value = x
    End With
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

(not tested at all).

 

[Bob_R]

That code worked. Many thanks.