vba pivot table

[VegasNath]

I'm brand new to pivot tables, but I will be required to use them lots going forward. I'm looking to adopt a vba approach for speed.

I would like to:

Double click anywhere within a data range.
Automatically find the used data range for the pivot.
Always on a separate sheet, renaming the pivot sheet to the source sheet + pivot. EG: Bal pivot.

Code: Select all

    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Nominal", _
        ColumnFields:="Company"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Net")
        .Orientation = xlDataField
        .NumberFormat = "#,##0.00"
    End With

Always on a separate sheet, renaming the pivot sheet to the source sheet + pivot. EG: Bal pivot.

May I ask for some assistance in setting up the basic concept? Many Thanks

[HansV]

I'm sorry, I don't understand your question. Could you provide more detailed information?

For example, do you want to create a new pivot table or modify an existing one? That's not clear to me from your description.
And what does the sample code mean?

[VegasNath]

I will always be creating a new pivot table. The sample code is what I got from the macro recorder. They show the column, row and body information to be used in the pivot. I will have a regular need for creating the same pivot in many different workbooks. Please let me know if you require any other info. Thanks

[HansV]

Here is code that will create a blank pivot table.
If you want to add row fields etc., you'll have to provide the necessary information - this may be different from sheet to sheet. Or do the data tables always have the same format, e.g. first column = page field, second column = row field, third column = column field, fourth column = data field?

Code: Select all

Sub CreatePivotTable()
  Dim wshSrc As Worksheet
  Dim wshTrg As Worksheet
  Dim rngSrc As Range
  Dim rngTrg As Range
  Dim pvc As PivotCache
  Dim pvt As PivotTable
  Set wshSrc = ActiveSheet
  Set rngSrc = ActiveCell.CurrentRegion
  Set wshTrg = Worksheets.Add(After:=wshSrc)
  wshTrg.Name = wshSrc.Name & " Pivot"
  Set rngTrg = wshTrg.Range("A3")
  Set pvc = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=rngSrc)
  Set pvt = pvc.CreatePivotTable( _
    TableDestination:=rngTrg)
End Sub

[VegasNath]

Hans,

I need the row, column and data fields to be driven by the column headers "Nominal", "Company" & "Net", if that is possible?

[HansV]

Does this do what you want?

Code: Select all

Sub CreatePivotTable()
  Dim wshSrc As Worksheet
  Dim wshTrg As Worksheet
  Dim rngSrc As Range
  Dim rngTrg As Range
  Dim pvc As PivotCache
  Dim pvt As PivotTable
  Dim pvf As PivotField
  Set wshSrc = ActiveSheet
  Set rngSrc = ActiveCell.CurrentRegion
  Set wshTrg = Worksheets.Add(After:=wshSrc)
  wshTrg.Name = wshSrc.Name & " Pivot"
  Set rngTrg = wshTrg.Range("A3")
  Set pvc = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=rngSrc)
  Set pvt = pvc.CreatePivotTable( _
    TableDestination:=rngTrg)
  pvt.AddFields _
    RowFields:="Nominal", _
    ColumnFields:="Company"
  Set pvf = pvt.PivotFields("Net")
  With pvf
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0.00"
  End With
End Sub

[VegasNath]

It does, Thankyou.

Except:
Set pvc = ActiveWorkbook.PivotCaches.Create( _
should be:
Set pvc = ActiveWorkbook.PivotCaches.Add( _

I think?

[HansV]

Brilliant move by Microsoft! In Excel 2003 and before, the method is PivotCaches.Add, but in Excel 2007 (which I'm using at the moment), it's PivotCaches.Create

So if you're using Excel 2003, you should indeed use PivotCaches.Add.

[VegasNath]

Ooh, that's helpful, especially when at work I could be using either.... Cheers MS!

One small addition if I may...

Instead of:
Set pvf = pvt.PivotFields("Net")

Would it be possible to request that the user (me) select the header for the data field (which would normally be "Net" but could be something else), and assign the text from the selected cell to pvf?

[HansV]

Near the beginning of the macro, add the lines

Code: Select all

  Dim strField As String
  strField = InputBox( _
    Prompt:="Enter the name of the data field", _
    Title:="Create pivot table", _
    Default:="Net")
  If strField = "" Then
    MsgBox "You didn't specify a field!", vbCritical
    Exit Sub
  End If

and change the line you mentioned to

Code: Select all

  Set pvf = pvt.PivotFields(strField)

[HansV]

Ooh, that's helpful, especially when at work I could be using either.... Cheers MS!

Fortunately, .Add will still work in Excel 2007 - it's a hidden method of the PivotCaches object. So you don't need to modify your code for users of Excel 2007.

[VegasNath]

Great, Thanks Hans.