I use Ctrl+[
all the time to go to the precedents of the ActiveCell. The SourceData for a PivotTable isn’t a precedent, but wouldn’t it be nice if there was a shortcut to go there? Yes, it would.
Until now, if I wanted to see the SourceData for a PivotTable, I would use Alt, J, T, I, D
to activate the Ribbon control for changing the source data (PivotTable Tools – Options – Change Data Source – Change Data Source). That would open the Change PivotTable Data Source dialog. The focus on that dialog is a RefEdit control so Excel navigates to the data. I don’t actually want to change it, though, so I hit ESC and it takes me back to the PivotTable. But I make a note of where it is and I use Ctrl+G
or simply navigate to where it is.
I decide to repurpose Ctrl+[
to do the deed.
1 2 3 4 5 6 7 8 9 |
Public Sub SelectPrecedents() gclsAppEvents.AddLog "^[", "SelectPrecedents" On Error Resume Next Selection.DirectPrecedents.Select Application.Goto ActiveCell.PivotTable.SourceData End Sub |
First, I recorded a macro to see what happens when I press Ctrl+[
. That became the first line after the error handler. Rather than doing a bunch of checking to see whether the ActiveCell was in a PivotTable, whether the PivotTable source was a Range, and a bunch of other stuff that I can’t even predict, I just attempt to Goto the SourceData. If it works, then it was possible. If there’s some reason I can’t, it just fails silently.
Good idea!
As of today, so does my RefTreeAnalyser utility.
http://jkp-ads.com/RefTreeAnalyser.asp
Just note this doesn’t work if your pivot table is coming from Excel’s data model. I suppose it would fail silently in that case.
As edh commented, the macro does not work for Pivot tables coming from Excel’s data model. However, the data source is still listed under the “Change Data Source” dialog box. Is there a macro that could pull the sheet info from the change data source dialog box and goto it?
Robert: that’s only true if your PivotTable only has fields in it from one Table. As soon as you add a field from another Table, then the “Change Data Source” icon is greyed out.
Dick…that could cause you some grief. Say I’ve got A2 active – which is clearly not a PivotTable. I want to go to the precedents of the formula in that cell, to see what it connects to. So I use your repurposed Ctrl+[ shortcut. Did I end up at the precedents of A2? Not necessarily. I could have been unknowingly transported to the source range of some PivotTable that A2 happened to be pointed at, without even being aware that there was a PivotTable.
@Jeff: Only if the activecell is within a pivottable will the command take you to the source data of that pivottable.
Not necessarily the case. Here’s what I mean. Say I have source data in A1:A4, and a PivotTable based on that source data in C1:C4, and in E2 I have the formula =C2.
If I make E2 the active cell, and hit the Ctrl+[ trigger, then the line Selection.DirectPrecedents.Select will make C3 the active cell, which is within the PivotTable. And then straight away the line Application.Goto ActiveCell.PivotTable.SourceData will take me to A2:A4, which is the PivotTable Source Data.
There’s no “silent fail”: Both lines get executed.
The intent of the code is to either take you to a cell’s precedents, or – if the active cell is a PivotTable – take you to the PivotTable’s data source. But in this edge-case, that doesn’t happen, because you move twice, without necessarily being aware of the fact. . You started off in a cell that was *not* a PivotTable, and you ended up in a PivotTable’s source data.
One possible lazy fix:
Good catch Jeff. Of course I designed it that way as a feature, not a bug. :)