Sometime in the mid-1990s, a man named Lyle Lanley walked into Microsoft with an idea. Probably the greatest…. Oh, it’s more of an Apple idea. “Just tell us the idea and we’ll implement it”, said Bill Gates.
I’m on a quest to rid my life of wizards. The wizards that Microsoft seems to use for every single command. It all started with fixing the find dialog as I grew tired of clicking the Options button. Then I saw an opportunity to speed up Text to Columns which is unnecessarily wizardish. Today’s victim is the Pivot Table.
Have you seen the wizard for Pivot tables? They couldn’t put that on one screen? Well I say, No more! I just want to make a pivot table:
- from an Excel range
- that I’ve already selected
- and I want it on a new sheet
Almost always. Unless I need something out of the ordinary, I’m using this code:
Dim rData As Range
Dim shNew As Worksheet
Dim pcNew As PivotCache
Dim rCell As Range
Dim lFieldCnt As Long
Dim ptNew As PivotTable
Const sFIELD As String = “Field”
‘Make sure a range is selected
If TypeName(Selection) = “Range” Then
Set rData = Selection.CurrentRegion
Set shNew = rData.Parent.Parent.Sheets.Add
‘put column headers in blank cells
lFieldCnt = 1
For Each rCell In rData.Rows(1).Cells
If IsEmpty(rCell.Value) Then
rCell.Value = sFIELD & lFieldCnt
lFieldCnt = lFieldCnt + 1
Set pcNew = shNew.Parent.PivotCaches.Add(xlDatabase, rData)
Set ptNew = shNew.PivotTables.Add(pcNew, shNew.Cells(1))
ptNew.AddFields rData.Rows(1).Cells(1).Text, rData.Rows(1).Cells(2).Text
Those last two lines add row, column, and data fields from the first three columns of the data. I don’t like that, as it’s prone to error. What I like less is what I get when I exclude those lines.
Compare four blank blue boxes to what you get via the user interface
I don’t like the four blank squares and I don’t like using the first three columns. I have to find something better.