Dear Fellow Stackoverflow-ians,
I come to you with another problem. I'm creating a drilldown on a varying PivotTable by VBA. I'm looking to drilldown on the Total cell always, and referring to it as i did in my code doesn't work when my PivotTable expands. How do i refer to a cell in my PivotTable without using a Cell-refering.
Code is as following:
Sheets("16-Compliancy-Extract").Select
Range("H14").Select
Selection.ShowDetail = True
Selection.Copy
H14 is the cell i'm referring to. Problem is that this Cell-"Adress" keeps changing when i'm dumping new data in to the PivotTable.
How do i name it decently?
Fixed it with this: Where it searches for the last filled cell in the same column as my GrandTotal, which i was trying to retreive.
Dim rng As Range
Dim pt As PivotTable
Set pt = Sheets("16-Compliancy-Extract").PivotTables("Draaitabel1")
Set rng = pt.ColumnRange
rng.Cells(rng.Cells.Count).End(xlDown).ShowDetail = True