ExcelBanter - 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!