asked on vba to find last filled cell in the "Grand Total" row
Using VBA, I have to find and add the totals in two different pivot tables (that could be in different columns.)
I would like to find "Grand Total" in column A (Will always be column A)
then find the last filled column in that row to get the total.
Then I need to find the "Grand Total" in column I (Will always be column I)
then find the last filled column in that row to get the total.
These two totals are added together.
Can anyone help? Thank you!!! :)
VBA
Last Comment
Martin Liss
Can you supply a sample workbook or even show a picture of the sheet with the tables?
Hi,
The following can give you some hints.
The idea is to retrieve the 1st cell of each pivot table, then a function can retrieve the desired cells:
The following can give you some hints.
The idea is to retrieve the 1st cell of each pivot table, then a function can retrieve the desired cells:
Option Explicit
Sub test()
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(2) '// Adjust to fit your needs
Dim rng As Excel.Range
Set rng = ws.Range("A1") '// adjust to fit your needs
Dim lastCell_A As Excel.Range
Set lastCell_A = GetPivotTableLastCell(rng.PivotTable)
Set ws = wb.Worksheets(3) '// Adjust to fit your needs
Set rng = ws.Range("I1") '// adjust to fit your needs
Dim lastCell_I As Excel.Range
Set lastCell_I = GetPivotTableLastCell(rng.PivotTable)
Dim sum As Double '// adjust to fit your needs
sum = lastCell_A.Value + lastCell_I.Value
End Sub
Public Function GetPivotTableLastCell(ByRef pv As Excel.PivotTable) As Excel.Range
Dim rng As Excel.Range
Set rng = pv.TableRange1
Dim lastCell As Excel.Range
Set lastCell = rng.Cells(rng.Cells.Count)
Set GetPivotTableLastCell = lastCell
End Function
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Using VBA...