2

I found a weird stuation in my Excel file and just want to know how the creater made it and how to automatically solve it.

Someone keep sending me an excel file with only one sheet which contains one Pivot table. The weird thing is that I cannot find the data source which this Pivot Table should link to.

Even I tried to click "unhide" button, there is no hinden sheet in this file.

However, when I double click the cell in this Pivot, there will be one new sheet appear in this file. And this new sheet is the data source of this Pivot.

Is it possible to create a Macro to call out this hidden sheet without I double click it manually?

Thanks in advance.

codingsnake99
  • 146
  • 1
  • 10
  • It *could* be that the data is only in the PivotCache, but for an alternative: if you hit [Alt]+[F11] to open the VBA Project, is there a Worksheet set to "Very Hidden"? (The "Visible" property has 3 settings: "Visible", "Hidden" - which you can unhide by right-clicking the tabs and selecting "Unhide" or "Very Hidden", where it doesn't even show up in the list of Tabs to unhide) – Chronocidal Oct 24 '18 at 16:16

1 Answers1

1

The data is likely stored in a PivotCache - this data is not found on a worksheet but is saved as part of the file.

https://trumpexcel.com/pivot-cache-excel/

See previous question here on SO:

Recreate Source Data from PivotTable Cache

Alternatively, if you are comfortable with XML and your workbook is saved in xlsx format, you can unzip the file (add a .zip extension and double-click) and find the pivotcache details stored as XML in pivotCacheDefinition1.xml and pivotCacheRecords1.xml in the xl\pivotCache folder.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125