VBA How to extract specific values from pivot table

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
201
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:


Sum of Amount

Period YearPeriod Month









2,0152,015 Total2,016
2,016 Total2,017
2,017 TotalGrand Total
Account GLAccount NumberAccount Name12
312
36

10000 - Assets10002Asset - Cash627,507.78627,507.78(133,762.24)(39,504.92)77,296.43(102,026.70)(101,441.51)(203,468.21)501,336.00

10003Asset - Accounts Receivable15,894.8715,894.87(17,155.42)832.42(8,658.77)(3,759.14)(1,810.38)(5,569.52)1,666.58

10004Asset - Prepaid Expense55,427.3755,427.3793,656.97(6,789.58)4,809.06(164.44)29,220.3629,055.9289,292.35

10006Asset - Building/Land9,301,947.739,301,947.734,679.75(186,662.25)(1,011,891.22)6,889.355,960.0012,849.358,302,905.86

10008Asset - Accumulated Depreciation(1,618,609.59)(1,618,609.59)(39,487.03)154,436.7035,975.64(35,167.67)(35,167.67)(70,335.34)(1,652,969.29)
10000 - Assets Total

8,382,168.168,382,168.16(92,067.97)(77,687.63)(902,468.86)(134,228.60)(103,239.20)(237,467.80)7,242,231.50
20000 - Liabilities20001Liabilities - Accounts Payable(71,152.00)(71,152.00)(203.40)38,090.9611,785.46(7,040.08)14,455.967,415.88(51,950.66)

20002Liabilities - Deferred Income(23,659.97)(23,659.97)(2,314.22)(1,000.00)(265.93)(1,421.98)(59.93)(1,481.91)(25,407.81)

20003Liabilities - Mortgage(562,033.87)(562,033.87)

562,033.87


0.00

20004Liabilities - Other Payable(357,380.06)(357,380.06)5,553.132,157.8936,305.964,297.675,759.5910,057.26(311,016.84)
20000 - Liabilities Total

(1,014,225.90)(1,014,225.90)3,035.5139,248.85609,859.36(4,164.39)20,155.6215,991.23(388,375.31)
30000 - Equity30001Equity - Partner Capital67,270.7267,270.72





67,270.72

30003Equity - Total Capital Contributions(15,333,959.47)(15,333,959.47)





(15,333,959.47)

30004Equity - Total Accumulated Earnings7,898,746.497,898,746.4989,032.4638,438.78(443,008.10)138,392.9983,083.58221,476.577,677,214.96

30005Equity - Total Distributions0.000.000.00
735,617.60
0.000.00735,617.60
30000 - Equity Total

(7,367,942.26)(7,367,942.26)89,032.4638,438.78292,609.50138,392.9983,083.58221,476.57(6,853,856.19)
40000 - Op Revenues40001Total Revenue(316,411.46)(316,411.46)(78,844.98)(68,777.70)(300,071.68)(68,974.25)(69,003.51)(137,977.76)(754,460.90)

40002Other Revenue(12,330.82)(12,330.82)(643.18)(873.46)89,676.97(2,090.85)(2,098.41)(4,189.26)73,156.89
40000 - Op Revenues Total

(328,742.28)(328,742.28)(79,488.16)(69,651.16)(210,394.71)(71,065.10)(71,101.92)(142,167.02)(681,304.01)
45000 - Other Revenue45002Other Revenue - Gain on Investment - Realized



(921,988.66)


(921,988.66)
45000 - Other Revenue Total





(921,988.66)


(921,988.66)
50000 - Op Expenses - Recoverable50001Expense - Administrative4,571.134,571.13671.151,335.364,257.981,360.601,237.132,597.7311,426.84

50002Expenses - Operating4,508.724,508.72626.54100.001,033.03584.92203.00787.926,329.67
50000 - Op Expenses - Recoverable Total

9,079.859,079.851,297.691,435.365,291.011,945.521,440.133,385.6517,756.51

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
Code:
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

Board Regular
Joined
Dec 1, 2011
Messages
201
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

New Member
Joined
Jan 8, 2018
Messages
2

ADVERTISEMENT

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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

Board Regular
Joined
Dec 1, 2011
Messages
201

ADVERTISEMENT

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:

Code:
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

Board Regular
Joined
Dec 1, 2011
Messages
201
Hello,

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

Code:
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:

Code:
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
 
Last edited:

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Hello,

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

Code:
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:

Code:
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" ???
 

Watch MrExcel Video

Forum statistics

Threads
1,108,860
Messages
5,525,257
Members
409,639
Latest member
nithin49

This Week's Hot Topics

Top