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
End If
Next rCell
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
ptNew.AddDataField ptNew.PivotFields(rData.Rows(1).Cells(3).Text)
End If
End Sub
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.
Dick- Try this. not the most elegant code, but gets the job done.
Sub PivotCreate()
‘ Creates it as the same worksheet template
‘ Renames it as the current sheet, adding -pt
‘ Turns off autoformat
‘ Puts the default field orientation as columns, not rows
MaxX = Selection.Columns.Count
MaxY = Selection.Rows.Count
Test = MaxX + MaxY
If (Test 28) Then
NewSheet = Left(CurrentSheet, 28) & “-pt”
Else
NewSheet = CurrentSheet & “-pt”
End If
Test = True
Do While (Test = True)
Test = DoesWorkSheetExist(NewSheet)
‘MsgBox NewSheet & ” exists? ” & Test & Len(NewSheet)
If (Test = True) Then
If (Len(NewSheet) > 28) Then
NewSheet = Left(NewSheet, 28) & “-pt”
Else
NewSheet = NewSheet & “-pt”
End If
If (Len(NewSheet) = 31) Then
MsgBox “Ran out of space for the sheet name! Stopping Macro.”
Exit Sub
End If
End If
Loop
Sheets.Add Type:=”Worksheet”
ActiveSheet.Name = NewSheet
ActiveSheet.Move After:=Sheets(CurrentSheet)
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=InputRange).CreatePivotTable _
TableDestination:=ActiveSheet.Range(“B5?), _
DefaultVersion:=xlPivotTableVersion10
Range(“B5?).Select
ActiveSheet.PivotTables(1).HasAutoFormat = False
ActiveSheet.PivotTables(1).NullString = “0?
ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables(1).PivotFields(1), “Sum of A”, xlSum
ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables(1).PivotFields(2), “Sum of B”, xlSum
ActiveSheet.PivotTables(1).DataPivotField.Orientation = xlColumnField
ActiveSheet.PivotTables(1).DataPivotField.Position = 1
ActiveSheet.PivotTables(1).DataPivotField.Orientation = xlHidden
End Sub
I thought you were in love with those keyboard shortcuts. In XL07, it’s just Alt-N, V, Enter, Enter. The wizard flashes on the screen and disappears before it has a chance to annoy you. I forgot the 2003 equivalent, but I remember something similar existed.
Dick wrote: “…just want to make a pivot table:
* from an Excel range
* that I’ve already selected
* and I want it on a new sheet
…”
That’s the problem. There are two different scenarios for my PTs and neither matches yours.
1) Select a cell in the source range, put the PT on the same worksheet.
2) Put the PT on the current worksheet, the source is another sheet, more often than not in another workbook.
Also, FWIW, in 2007, there’s a single dialog box. No wizard.
You dont have to go through the wizard. Just say finish on the first dialog.
Alt + D + P + F
Dick,
Using the PivotCache and defining the version, you’d get the classic layout instead of the “dumbed down” version of it. Something like this:
Sub CreatePivotTable()
Dim pcNew As PivotCache
Dim ptNew As PivotTable
Dim rData As Range
Const sPTNAME As String = “PT”
On Error GoTo Err_Handler
If TypeName(Selection) = “Range” Then
Set rData = Selection.CurrentRegion
Set pcNew = ThisWorkbook.PivotCaches.Add(xlDatabase, rData)
Set ptNew = pcNew.CreatePivotTable(TableDestination:=””, _
tablename:=sPTNAME & ThisWorkbook.PivotCaches.Count + 1, _
defaultversion:=xlPivotTableVersion10)
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, Err.Number
End Sub
“Have you seen the wizard for Pivot tables? They couldn’t put that on one screen?”
You realize that Rob B. made the PT wizard for Microsoft?
Tushar: The wiz is still available in 2007, it is one of the “Commands not in the ribbon”
Jan, not to be picky but the copyright is important “Commands not in the Ribbon © MS Corporation 2005-2010?
Alt+D+P brings up the old PivotTable Wizard.