How to extract Excel pivotTable total?

[bez999]

Hello

I have a pivot table that shows totals by merchant within card types. My data looks something like:
Merchant
Cardtype 444444 55555555
Visa 200 4400
Electron 1000 60
Mastercard 600 900


I need to pick up the values from specific totals e.g. the 1000 from cardtype electron for merchant 444444.

I have spent ages trying to find out how to do this and failed.

If anyone can help with this you'd save my life.

Thanks.

Regards
Chris Bezant

 

[SkipVought]

Hi,

Take a look at the TableRange1 property of the PivotTable object and the BottomRightCell property of the range.

Skip,
[sub]
[red][/red]
[/sub]

 

[bez999]

Thanks for responding Skip.

I have had a play with the debugger and browsed the help but I'm obviously having yet another thick day.

I assume I have to identify which piece of data I am interested in so I have my code as:

Set pvt = ActiveSheet.PivotTables("PivotTable1")
Temp = pvt.TableRange1.Range("cardtypedetected").BottomRightCell


BottomRightCell is not offered by the object prompter so I'm thinking I'm not at the right level to get it. Many of the other properties offered at this level actually result in errors.

I'd appreciate it if you can shed any more light, sir.

Regards
Chris Bezant

 

[SkipVought]

I'm sorry. I missdirected you.

with ActiveSheet.PivotTables("PivotTable1").tablerange1
  lLastRow = .row + .rows.count - 1
  iLastCol = .column + .columns.count - 1
end with

Skip,
[sub]
[red][/red]
[/sub]

 

[bez999]

Thanks Skip.

I can see that using .row, .column and your lLastRow, lLastColumn I can determine the range of my data. I will then have to find my values within that.

I was expecting to be able to extract data pertaining to my data grouping by using the names such as CardType and MerchantID.

Using Find in the range will work though, so many thanks for your input.

Regards
Chris Bezant

 

[SkipVought]

Have you recorded and code?

Take a look at the Find Method.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Have you recorded and code?

Take a look at the Find Method.

Skip,
[sub]
[red][/red]
[/sub]

 

[combo]

With new in xp GetPivotData method, assuming pivot table on the first workshet and one data field:

Dim PT As PivotTable
Set PT = Worksheets(1).PivotTables(1)
MsgBox PT.GetPivotData(PT.DataFields(1).Name, "Merchant
", 444444, "Cardtype", "Electron")

combo

 

[bez999]

Hello Combo

That's exactly the sort of thing I expected to be able to do but unfortunately I cannot get it to work.

I have taken an image of my pivot table but I cannot paste it here. Would it be possible to email it to you so that you can see exactly what I am trying to do?

I must be really thick because I just cannot figure out how to do this. It's very frustrating to know that I'm so close.

Thanks for your help.

Regards
Chris Bezant

 

[combo]

Hi Chris
First of all the code will work only in excel xp or later and if you have this value on the screen.
I would recommend to start with simple pivot table and read vba help concerning GetPivotData method (and generally pivot table). Mind that DataField(s) is a specific pivot field(s) that is placed on data area. Check if you have proper reference; as you do not specify it, I assumed you used first.
The syntax is:
PivotTableReference(DataFieldName, Field1Name, Item1Value,...)
I will be rather short of time next week, anyway if you like to contact me, go to any of my faqs (see my profile) and send a comment.

combo

 

[Gavona]

Ken just pointed out in thread68-1326799 that there is a "Generate GetPivotData" button:
"Open the Pivot Table Toolbar (View/Toolbars/PivotTable) and go to the add/remove buttons option and put on the 'GetPivotData' button. Now literally just use the mouse to link into it and it will automatically pick up that field even if the data table changes size."

[GetPivotData exists as a worksheet function pre Excel2003 but had a slightly different format (a compatibility issue).]


Hoping the above may help you get GetPivotData to work.



Gavin

 

[combo]

GetPivotData came with excel xp, both as worksheet function and pivot table method in vba.

combo

 

[Gavona]

Sorry Combo, you are mistaken as I know from experience but you can also see here:

http://www.contextures.com/xlPivot06.html

"In Excel 2000 or Excel 97, you have to create the GetPivotData formula manually, by typing an equal sign, the function name, and the required arguments, just as you would for any other function."
In Excel 2002 onwards the "Generate GetPivotFata" was added.

What I found was that a GetPivotData formula generated by Excel 2003 returned an error in Excel 2000. When I changed the order of the parameters to make it work in Excel 2000 then it also worked in 2003.

Regards,


Gavin

 

[combo]

Gavin,
I pointed excel xp = excel 2002, it is not 2000 version. I do not work in excel 2003, but guess that functions available in xp work in 2003 too.

combo

 

[bez999]

Hi Guys
Thanks for all your input and your time.
I omitted to say that I am using Excel 2003. I tried to get combo’s solution to work but failed. I had already read up on the suggested items but still failed to understand how to do it.
I finally took Skip’s idea of establishing the range occupied by the data and used Find to locate the data I want. It may not be the most elegant solution but it works for what I am doing.
For what it’s worth here is my code:

Function GetPivotTableValue(pv As PivotTable, RowTitle As String, ColumnTitle As String)
Dim RowNumber As Long
Dim ColumnNumber As Long
Dim pvRange As Range

' Establish the range of the pivot table data
With pv.TableRange1
Set pvRange = Range(Cells(.Row, .Column), Cells(.Row + .Rows.Count - 1, .Column + .Columns.Count - 1))
End With

' Find the row title row
RowNumber = pvRange.Find(RowTitle).Row

' Find the column title column
ColumnNumber = pvRange.Find(ColumnTitle).Column

' Get the value from the intersection point of the range
' using the calculated row and column numbers
GetPivotTableValue = Cells(RowNumber, ColumnNumber)

End Function

Thanks again for helping.


Regards
Chris Bezant