Quick PivotTables

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:

Sub CreatePivotTable()
   
    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.

Posted in Uncategorized

9 thoughts on “Quick PivotTables

  1. 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

  2. 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.

  3. 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.

  4. You dont have to go through the wizard. Just say finish on the first dialog.
    Alt + D + P + F

  5. 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

  6. “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?

  7. Jan, not to be picky but the copyright is important “Commands not in the Ribbon © MS Corporation 2005-2010?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.