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
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 you’d pay for lunch!
www.eXtreme-eXcel.com
Don’t Let Anyone Lock You Out Of A Spreadsheet Again!
