How to derive (export) Pivot table base data

[ThrottleWorks]

Hi,

I am required to work on couple of Pivot tables.
One of the task is to export Pivot table data on a new sheet and procees it.

I do not have files yet, so I recorded the below mentioned code with a dummy Pivot table.

Can anyone please guide if this is the correct way to export data or there is another better alternative to it.

Selection.ShowDetail = True

 

[Luke M]

If by "export the data" you mean the same thing as when you double click on a pivotTable, then yes, you're on the right track. I'd replace the non-helpful Selection object with a more definitive range. For instance, here's a macro I wrote that returns full results of PivotTable. It's a bit dynamic as the size of PT can change depending on what user has selected.

Sub ShowPivotDetails()
Dim rngAll As Range
Dim rngGrand As Range
Dim pvt As PivotTable

'Finds the Grand Total cell in the PivotTable, and shows detail
'Note that my worksheet's code name is "wsPivot" and "ptMain" is the name
'of a pivot table on that sheet

Set pvt = wsPivot.PivotTables("ptMain")
Set rngAll = pvt.DataBodyRange

'Check if user has filtered everything away
If rngAll Is Nothing Then Exit Sub

'Sets rngGrand to the last cell in the range. I have both row and
'column Grand Totals turned on, so this cell will give me the details
'for the entire table

Set rngGrand = rngAll.Cells(rngAll.Cells.Count)

'Now that I have my cell, I show the details
rngGrand.ShowDetail = True
End Sub

 

[ThrottleWorks]

Hi Luke Sir, thanks a lot for the help.

 

[ThrottleWorks]

Hi,

I have multiple Pivot tables on a sheet, for example 10.

I need to export details of 5 Pivot tables out of these 10.

Each of these 5 Pivot tables has name of the table in the cell.

So for example if you refer cell B4 in the attached file, it says Pvt1.

That is the string I need to find in the file, once I get this string I need to export pivot table details for this pivot.

This way I need to find 5 different strings in the worksheet and export pivot tables accordingly.

My doubt is how do I identify pivots present in the worksheet.

Is there any way where I can find pivot tables, are pivot tables saved with internal name in excel.


I am not able to understand how should I proceed once I find the string (for example Pvt1).

Once I find this string I know pivot table is located exactly 4 cells below in the same column.


But no of columns in each pivot differs and they might change, they are not static.

So today the last column of pivot table 1 might be Column R tomorrow it might be Column X.


How do I export details based on this. Can anyone please help me in this.

 

[ThrottleWorks]

Hi,

I guess, I am able to do it with @Luke M Sir's code mentioned above.
It took me while to understand the code (my mistake entirely).

Will get back with results or issues.

PS - Hi @Luke M Sir, thanks a lot for the help and your valuable time. It's working.

Till now I was not aware how to search Pivot table names or they can be renamed manually.

All the Pivots I am trying to export are already named.
Now I can export my pivot tables with the code provided by you.

Yesterday I was getting confused about how do I search a particular pivot table in a worksheet.

Have a nice day ahead. You are .

Also, what if the Pivot table name is changed by user.

For example, today Pivot is named as "Yamaha" I am searching for "Yamaha" Pivot.
But in future if the user change the name to "Honda" my code will give bug right ?

In such case what should we do, is there any alternate to avoid this or we can simply tell the user not to change the Pivot name.

 

[Luke M]

You could try referring to it by the index number. Takes a little more work to figure out which PT you want, but then you're not name dependent.

First, easy example of index vs. names.
When you want to refer to a worksheet, you could give the name, or the index. Both of these lines should refer to same thing:

Worksheets("Sheet1").Select
Worksheets(1).Select

Assuming that Sheet1 is the 1st sheet in your workbook.

Now, you can do the same thing with PivotTables. You can do:

Set pvt = wsPivot.PivotTables("ptMain")
'or this
Set pvt = wsPivot.PivotTables(1)

The catch is figuring out the correct number to use. Once you have the number though, should be safe. The simplest approach to figure out which # the PT you want is, use a simple macro like:

Sub PT_Names()
Dim i As Long

For i = 1 To ActiveSheet.PivotTables.Count
    Debug.Print ActiveSheet.PivotTables(i).Name & " is #: " & i
Next i
End Sub

Run this macro, and it will tell you which names go with which number. Then you can use the numbers in your code.

 

[ThrottleWorks]

Hi @Luke M Sir, thanks a lot for the help and your valuable time.

I am checking this and will revert in case of any issues. Good night.