Getting the Source Range of a Pivot Table with VBA
I was pouring through Héctor Miguel Orozco Díaz’s filtering code recently and decided to rewrite it. I was hoping to make it shorter, but I couldn’t. I did move some code outside the main procedure, though, and I’ll be posting that code over the next few days.
In order to filter the source range of a pivot table, I had to know where that range lives. And that’s the subject of today’s post. The PivotTable object has a SourceData property that is a String representation of the Excel range. Upstream of this function, I test to make sure the pivot table is based on an Excel range, so you won’t see any of that code here.
Dim lBang As Long
Dim lClose As Long
Dim sAddress As String
Dim sSheet As String
Dim sSource As String
Dim rReturn As Range
Const sBANG As String = “!”
Const sCLOSE As String = “]”
sSource = Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)
lBang = InStr(1, sSource, sBANG)
lClose = InStr(1, sSource, sCLOSE)
If lBang > 0 Then
sAddress = Mid$(sSource, lBang + 1, Len(sSource))
If lClose > 0 Then
sSheet = Mid$(sSource, lClose + 1, lBang – lClose – 1)
Else
sSheet = Left$(sSource, lBang – 1)
End If
Set rReturn = pt.Parent.Parent.Sheets(sSheet).Range(sAddress)
Else
Set rReturn = pt.Parent.Range(sSource)
End If
Set GetRangeFromSourceData = rReturn
End Function
The objective here is to parse the SourceData string and convert it into a Range object. I pass in the PivotTable as an argument. The SourceData property returns something that looks like this:
I don’t know why it uses R1C1 notation, but there it is. My first step is to convert it that to A1 notation with the ConvertFormula method, which returns something like this:
I don’t need the workbook name because you can’t have the source in a different workbook than the pivot table unless you use External Data. But it’s there, so I have to deal with it. Now I just need to parse the string. I’m looking for two key characters in the string: The bang operator (!) separates the sheet name from the range reference, and the closing bracket (]) identifies the end of the workbook name. I attempt to find both of those characters’ positions and store them in lBang and lClose.
I could probably safely assume that there will always be a bang and a close bracket, but I test for it anyway. If there’s no bang, I assume the whole string is just a range reference. If there is a bang, I parse out the sheet name and the range reference and store them. If there’s no close bracket, I assume the string is in the format Sheet1!$A$1:$D$51. I haven’t actually found an instance where both weren’t there, but I didn’t know (and still don’t know for sure) that that would always be the case.
With the sheet name and range reference, I get the range like this:
The Parent of a PivotTable is a Worksheet. The Parent of that is the Workbook. Now I can get that source range into a variable in the calling procedure. That looks like this:
In the next post I’ll create an array of all the pivots that affect a particular cell within a pivot table.
Dick,
Could you just evaluate the range address once converted and get back a Range object?
i.e. Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
John, that works like a peach. All that work for nothing.
I also forgot to account for single quotes around the workbook/worksheet names.
You also need to account for the fact the the Pivot could be build on a Dynamic Range or a Table name