Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract pivot table raw data
Hi all,
I have an excel which the pivot table is created using query towards access file. and currently, i don't have the copy of the access file. Is there a way to retreive the raw data from the pivot table? Thanks in advance -- (^oo^) 百 as pure as snow |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract pivot table raw data
On Oct 17, 9:44Â*pm, Han Qiao
wrote: Hi all, I have an excel which the pivot table is created using query towards access file. and currently, i don't have the copy of the access file. Is there a way to retreive the raw data from the pivot table? Thanks in advance -- (^oo^) 百 as pure as snow Hi Han, Here is a macro I use to extract raw data from a simple matrix or pivot table. Note it only works with 2-dimensional tables but you could certainly extend it for nested, multi-dimensional tables. To use it, select the data cells in the pivot table, not including the headers or totals, then run the macro. Sub depivotise() ' Use this create a flat file from data arranged in a matrix (eg months in columns) ' this sets up the data for efficient use of a pivot table ' values in 1 column left and 1 row above will be used for field values Dim dest As Worksheet, org As Worksheet On Error GoTo depivotise_error Set org = ActiveWorkbook.ActiveSheet myRow = Selection.Row - 1 myCol = Selection.Column - 1 Set dest = ActiveWorkbook.Worksheets.Add x = 1 org.Activate For Each cell In Selection dest.Cells(x, 1).Value = cell.Offset(-cell.Row + myRow, 0).Value dest.Cells(x, 2).Value = cell.Offset(0, -cell.Column + myCol).Value dest.Cells(x, 3).Value = cell.Value x = x + 1 Next cell Exit Sub depivotise_error: If Err = 1004 Then MsgBox "selection cannot include column A or row 1. Process aborted." Exit Sub Else MsgBox Err & ": " & Error Resume Next End If End Sub HTH cheers, troy. Unprotect Any Spreadsheet€¦ Without The Password€¦ In Just Seconds Get eXL_unProtect today for less than youd pay for lunch! www.eXtreme-eXcel.com Dont Let Anyone Lock You Out Of A Spreadsheet Again! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract pivot table raw data
Hi
Just double click on the Grand Total cell at bottom right of the PT and a new sheet will be created with all of the source data from the Pivot Cache. -- Regards Roger Govier "Han Qiao" wrote in message ... Hi all, I have an excel which the pivot table is created using query towards access file. and currently, i don't have the copy of the access file. Is there a way to retreive the raw data from the pivot table? Thanks in advance -- (^oo^) ? as pure as snow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Client Refs from pivot table into another worksheet | Excel Worksheet Functions | |||
Extract data from a table | Excel Worksheet Functions | |||
HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE | New Users to Excel | |||
How to extract the month in a Pivot Table | Excel Worksheet Functions | |||
how do i extract data from pivot table to txt file | Excel Discussion (Misc queries) |