VBA How to extract specific values from pivot table

[Matador_24]

Hello,

I am starting to learn how to extract data from pivot tables. However, I have found some easy examples online but unfortunately I have a complex pivot table that constantly changes of number of rows and columns due to the month and year periods changing. Thus, I need to extract specific values from the pivot table using the first three columns (fields), I will send this specific values to another sheet. I can not use vlookup because the number of columns change constantly. Thus, I would like to use VBA to extract the below values from the pivot table, I am asking three different values so that I can see how to use the different fields considering different months, years and total/grand total combinations so that I can see the difference among them.

Could you please help me out with the code to extract the amount values in:
Blue:
I want to be able to extract and SAVE into a variable $ 1,666.58 from the table; thus, using 10003 (Account number field) and considering grand total as column.
Green:
I want to be able to extract and save $ 609,859.36 from the table; thus using 20000 - Liabilities Total (Account GL field) and considering 2016 total as column.
Red
I want to be able to extract $ 626.54 from the table by using Expenses Operating (Account name field) and considering the period month 3 of year 2016.

Thanks for your help in advance! here is the table:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1539"]
<tbody>[TR]
[TD]Sum of Amount[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Period Year[/TD]
[TD]Period Month[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]2,015[/TD]
[TD]2,015 Total[/TD]
[TD="align: right"]2,016[/TD]
[TD]
[/TD]
[TD]2,016 Total[/TD]
[TD="align: right"]2,017[/TD]
[TD]
[/TD]
[TD]2,017 Total[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Account GL[/TD]
[TD]Account Number[/TD]
[TD]Account Name[/TD]
[TD]12[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]
[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10000 - Assets[/TD]
[TD]10002[/TD]
[TD]Asset - Cash[/TD]
[TD="align: right"]627,507.78[/TD]
[TD="align: right"]627,507.78[/TD]
[TD="align: right"](133,762.24)[/TD]
[TD="align: right"](39,504.92)[/TD]
[TD="align: right"]77,296.43[/TD]
[TD="align: right"](102,026.70)[/TD]
[TD="align: right"](101,441.51)[/TD]
[TD="align: right"](203,468.21)[/TD]
[TD="align: right"]501,336.00[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10003[/TD]
[TD]Asset - Accounts Receivable[/TD]
[TD="align: right"]15,894.87[/TD]
[TD="align: right"]15,894.87[/TD]
[TD="align: right"](17,155.42)[/TD]
[TD="align: right"]832.42[/TD]
[TD="align: right"](8,658.77)[/TD]
[TD="align: right"](3,759.14)[/TD]
[TD="align: right"](1,810.38)[/TD]
[TD="align: right"](5,569.52)[/TD]
[TD="align: right"]1,666.58[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10004[/TD]
[TD]Asset - Prepaid Expense[/TD]
[TD="align: right"]55,427.37[/TD]
[TD="align: right"]55,427.37[/TD]
[TD="align: right"]93,656.97[/TD]
[TD="align: right"](6,789.58)[/TD]
[TD="align: right"]4,809.06[/TD]
[TD="align: right"](164.44)[/TD]
[TD="align: right"]29,220.36[/TD]
[TD="align: right"]29,055.92[/TD]
[TD="align: right"]89,292.35[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10006[/TD]
[TD]Asset - Building/Land[/TD]
[TD="align: right"]9,301,947.73[/TD]
[TD="align: right"]9,301,947.73[/TD]
[TD="align: right"]4,679.75[/TD]
[TD="align: right"](186,662.25)[/TD]
[TD="align: right"](1,011,891.22)[/TD]
[TD="align: right"]6,889.35[/TD]
[TD="align: right"]5,960.00[/TD]
[TD="align: right"]12,849.35[/TD]
[TD="align: right"]8,302,905.86[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10008[/TD]
[TD]Asset - Accumulated Depreciation[/TD]
[TD="align: right"](1,618,609.59)[/TD]
[TD="align: right"](1,618,609.59)[/TD]
[TD="align: right"](39,487.03)[/TD]
[TD="align: right"]154,436.70[/TD]
[TD="align: right"]35,975.64[/TD]
[TD="align: right"](35,167.67)[/TD]
[TD="align: right"](35,167.67)[/TD]
[TD="align: right"](70,335.34)[/TD]
[TD="align: right"](1,652,969.29)[/TD]
[/TR]
[TR]
[TD]10000 - Assets Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]8,382,168.16[/TD]
[TD="align: right"]8,382,168.16[/TD]
[TD="align: right"](92,067.97)[/TD]
[TD="align: right"](77,687.63)[/TD]
[TD="align: right"](902,468.86)[/TD]
[TD="align: right"](134,228.60)[/TD]
[TD="align: right"](103,239.20)[/TD]
[TD="align: right"](237,467.80)[/TD]
[TD="align: right"]7,242,231.50[/TD]
[/TR]
[TR]
[TD]20000 - Liabilities[/TD]
[TD]20001[/TD]
[TD]Liabilities - Accounts Payable[/TD]
[TD="align: right"](71,152.00)[/TD]
[TD="align: right"](71,152.00)[/TD]
[TD="align: right"](203.40)[/TD]
[TD="align: right"]38,090.96[/TD]
[TD="align: right"]11,785.46[/TD]
[TD="align: right"](7,040.08)[/TD]
[TD="align: right"]14,455.96[/TD]
[TD="align: right"]7,415.88[/TD]
[TD="align: right"](51,950.66)[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]20002[/TD]
[TD]Liabilities - Deferred Income[/TD]
[TD="align: right"](23,659.97)[/TD]
[TD="align: right"](23,659.97)[/TD]
[TD="align: right"](2,314.22)[/TD]
[TD="align: right"](1,000.00)[/TD]
[TD="align: right"](265.93)[/TD]
[TD="align: right"](1,421.98)[/TD]
[TD="align: right"](59.93)[/TD]
[TD="align: right"](1,481.91)[/TD]
[TD="align: right"](25,407.81)[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]20003[/TD]
[TD]Liabilities - Mortgage[/TD]
[TD="align: right"](562,033.87)[/TD]
[TD="align: right"](562,033.87)[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]562,033.87[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]20004[/TD]
[TD]Liabilities - Other Payable[/TD]
[TD="align: right"](357,380.06)[/TD]
[TD="align: right"](357,380.06)[/TD]
[TD="align: right"]5,553.13[/TD]
[TD="align: right"]2,157.89[/TD]
[TD="align: right"]36,305.96[/TD]
[TD="align: right"]4,297.67[/TD]
[TD="align: right"]5,759.59[/TD]
[TD="align: right"]10,057.26[/TD]
[TD="align: right"](311,016.84)[/TD]
[/TR]
[TR]
[TD]20000 - Liabilities Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](1,014,225.90)[/TD]
[TD="align: right"](1,014,225.90)[/TD]
[TD="align: right"]3,035.51[/TD]
[TD="align: right"]39,248.85[/TD]
[TD="align: right"]609,859.36[/TD]
[TD="align: right"](4,164.39)[/TD]
[TD="align: right"]20,155.62[/TD]
[TD="align: right"]15,991.23[/TD]
[TD="align: right"](388,375.31)[/TD]
[/TR]
[TR]
[TD]30000 - Equity[/TD]
[TD]30001[/TD]
[TD]Equity - Partner Capital[/TD]
[TD="align: right"]67,270.72[/TD]
[TD="align: right"]67,270.72[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]67,270.72[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]30003[/TD]
[TD]Equity - Total Capital Contributions[/TD]
[TD="align: right"](15,333,959.47)[/TD]
[TD="align: right"](15,333,959.47)[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](15,333,959.47)[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]30004[/TD]
[TD]Equity - Total Accumulated Earnings[/TD]
[TD="align: right"]7,898,746.49[/TD]
[TD="align: right"]7,898,746.49[/TD]
[TD="align: right"]89,032.46[/TD]
[TD="align: right"]38,438.78[/TD]
[TD="align: right"](443,008.10)[/TD]
[TD="align: right"]138,392.99[/TD]
[TD="align: right"]83,083.58[/TD]
[TD="align: right"]221,476.57[/TD]
[TD="align: right"]7,677,214.96[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]30005[/TD]
[TD]Equity - Total Distributions[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD]
[/TD]
[TD="align: right"]735,617.60[/TD]
[TD]
[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]735,617.60[/TD]
[/TR]
[TR]
[TD]30000 - Equity Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](7,367,942.26)[/TD]
[TD="align: right"](7,367,942.26)[/TD]
[TD="align: right"]89,032.46[/TD]
[TD="align: right"]38,438.78[/TD]
[TD="align: right"]292,609.50[/TD]
[TD="align: right"]138,392.99[/TD]
[TD="align: right"]83,083.58[/TD]
[TD="align: right"]221,476.57[/TD]
[TD="align: right"](6,853,856.19)[/TD]
[/TR]
[TR]
[TD]40000 - Op Revenues[/TD]
[TD]40001[/TD]
[TD]Total Revenue[/TD]
[TD="align: right"](316,411.46)[/TD]
[TD="align: right"](316,411.46)[/TD]
[TD="align: right"](78,844.98)[/TD]
[TD="align: right"](68,777.70)[/TD]
[TD="align: right"](300,071.68)[/TD]
[TD="align: right"](68,974.25)[/TD]
[TD="align: right"](69,003.51)[/TD]
[TD="align: right"](137,977.76)[/TD]
[TD="align: right"](754,460.90)[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]40002[/TD]
[TD]Other Revenue[/TD]
[TD="align: right"](12,330.82)[/TD]
[TD="align: right"](12,330.82)[/TD]
[TD="align: right"](643.18)[/TD]
[TD="align: right"](873.46)[/TD]
[TD="align: right"]89,676.97[/TD]
[TD="align: right"](2,090.85)[/TD]
[TD="align: right"](2,098.41)[/TD]
[TD="align: right"](4,189.26)[/TD]
[TD="align: right"]73,156.89[/TD]
[/TR]
[TR]
[TD]40000 - Op Revenues Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](328,742.28)[/TD]
[TD="align: right"](328,742.28)[/TD]
[TD="align: right"](79,488.16)[/TD]
[TD="align: right"](69,651.16)[/TD]
[TD="align: right"](210,394.71)[/TD]
[TD="align: right"](71,065.10)[/TD]
[TD="align: right"](71,101.92)[/TD]
[TD="align: right"](142,167.02)[/TD]
[TD="align: right"](681,304.01)[/TD]
[/TR]
[TR]
[TD]45000 - Other Revenue[/TD]
[TD]45002[/TD]
[TD]Other Revenue - Gain on Investment - Realized[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](921,988.66)[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](921,988.66)[/TD]
[/TR]
[TR]
[TD]45000 - Other Revenue Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](921,988.66)[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"](921,988.66)[/TD]
[/TR]
[TR]
[TD]50000 - Op Expenses - Recoverable[/TD]
[TD]50001[/TD]
[TD]Expense - Administrative[/TD]
[TD="align: right"]4,571.13[/TD]
[TD="align: right"]4,571.13[/TD]
[TD="align: right"]671.15[/TD]
[TD="align: right"]1,335.36[/TD]
[TD="align: right"]4,257.98[/TD]
[TD="align: right"]1,360.60[/TD]
[TD="align: right"]1,237.13[/TD]
[TD="align: right"]2,597.73[/TD]
[TD="align: right"]11,426.84[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]50002[/TD]
[TD]Expenses - Operating[/TD]
[TD="align: right"]4,508.72[/TD]
[TD="align: right"]4,508.72[/TD]
[TD="align: right"]626.54[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]1,033.03[/TD]
[TD="align: right"]584.92[/TD]
[TD="align: right"]203.00[/TD]
[TD="align: right"]787.92[/TD]
[TD="align: right"]6,329.67[/TD]
[/TR]
[TR]
[TD]50000 - Op Expenses - Recoverable Total[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]9,079.85[/TD]
[TD="align: right"]9,079.85[/TD]
[TD="align: right"]1,297.69[/TD]
[TD="align: right"]1,435.36[/TD]
[TD="align: right"]5,291.01[/TD]
[TD="align: right"]1,945.52[/TD]
[TD="align: right"]1,440.13[/TD]
[TD="align: right"]3,385.65[/TD]
[TD="align: right"]17,756.51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roderick_E]

Are the names of these rows and columns always going to be what you're looking for? If so you could look for the row/column where it appears and then go from there.
This function returns the cell address of the UNIQUE string you're looking for.

Public Function findany(str As String, wsh As String) 'string and sheet name
Application.Volatile
On Error Resume Next
findany = 0
findany = ActiveWorkbook.Sheets(wsh).Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Address
findany = ThisWorkbook.Sheets(wsh).Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Address
Resume Next
End Function

 

[Matador_24]

Hello Roderick,

No, the rows and columns will change depending on the data of the pivot table, it is possible that sometimes there will be more rows (account numbers) and/or sometimes more columns (months), that is why, I can not use fixed row or column numbers and I prefer to extract using the row and column title regardless of the position of such value...

 

[Roderick_E]

Hello Matador, my findany is looking for a string you define; such as a column title. Once you have that you can determine where the data you want to extract relative to the column/row titles.

 

[hollami]

Is it possible to update a specific field in a pivot table and then save it to the data source location? For example a Pass /Fail field

 

[Roderick_E]

Is it possible to update a specific field in a pivot table and then save it to the data source location? For example a Pass /Fail field

A pivot table is build from a data set. All fields on the pivot table come one way another from the data set. So in short that answer is no.

 

[Matador_24]

Hello Roderick,

Thanks for your help. However, I would like to use a way to extract data using the pivot table fields. For instance, I found the way to extract the yearly totals in my example above using the 'Account Number'

This is an example of the code that worked:

LngValue = ActiveSheet.PivotTables(1).GetData("'Sum of Amount' 10002 2,016 Total")

Now the issue is that I can only extract the totals, meaning 2,015 Total, 2,016 Total, 2,017 Total and Grand Total columns values, which is fine because those are part of the data I want to extract, However, I need to extract the monthly info as well, I have tried replace in the example above: 2,016 Total by 2016 - 6 for instance but it shows error.... any idea how to use that code to extract the months?

Thanks!

Luis

 

[Matador_24]

Hello,

One update, after searching and testing, I had this code working:

LngAccount = ActiveSheet.PivotTables(1).GetPivotData("Sum of Amount", "Account Number", "10003", "Period Year", "2,016", "Period Month", "3")

However, when I try to use a variable it shows an error:

Dim LngAccount as Long
LngAccount = 10003
MsgBox "xx" & LngAccount & "xx"
LngValue = ActiveSheet.PivotTables(1).GetPivotData("Sum of Amount", "Account Number", LngAccount, "Period Year", "2,016", "Period Month", "3")

Does anybody know how to use a variable with the above code??????
note that the result of the msgbox is xx10003xx, meaning that the value of LngAccount is 10003

Thanks

Luis

 

[Matador_24]

Hello,

Some help here please!

Many thankssssssssssssss!

 

[Roderick_E]

Hello,

One update, after searching and testing, I had this code working:

LngAccount = ActiveSheet.PivotTables(1).GetPivotData("Sum of Amount", "Account Number", "10003", "Period Year", "2,016", "Period Month", "3")

However, when I try to use a variable it shows an error:

Dim LngAccount as Long
LngAccount = 10003
MsgBox "xx" & LngAccount & "xx"
LngValue = ActiveSheet.PivotTables(1).GetPivotData("Sum of Amount", "Account Number", LngAccount, "Period Year", "2,016", "Period Month", "3")

Does anybody know how to use a variable with the above code??????
note that the result of the msgbox is xx10003xx, meaning that the value of LngAccount is 10003

Thanks

Luis

LngAccount = "xx" & 10003 & "xx" ???