asked on

How do I code the creation of a pivot table in vb

I tried using the macro recorder to understand how I can code a pivot table creation in vb but it doesn't work when I try reusing the macro.

I says "Invalid procedure, call or argument".

Please let me know what is wrong.

Sub Macro1()
'
' Macro1 Macro
'

'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R4990C46", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable58", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable58").PivotFields("Original_Portfolio")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable58").AddDataField ActiveSheet.PivotTables( _
        "PivotTable58").PivotFields("NPV"), "Sum of NPV", xlSum
End Sub

Open in new window

nutsch

Try

Open in new window

Sub Macro1()
'
' Macro1 Macro
'
dim sPivotName as string, pt as pivottable

sPivotName="Pivot_" & format(now(),"ddmm_hhmmss")
'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        sheets("Sheet1!").cells(1,1,).currentregion, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=activesheet.cells(3,1), TableName:=sPivotName, DefaultVersion _
        :=xlPivotTableVersion12

set pt=ActiveSheet.PivotTables(spivotname)
    With pt.PivotFields("Original_Portfolio")
        .Orientation = xlColumnField
        .Position = 1
    End With
    pt.AddDataField pt.PivotFields("NPV"), "Sum of NPV", xlSum
End Sub

Open in new window

appc

ASKER

It says syntax error on this part:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sheets("Sheet1!").cells(1,1,).currentregion, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=activesheet.cells(3,1), TableName:=sPivotName, DefaultVersion _
:=xlPivotTableVersion12

nutsch

as it should, I had left an excess !!

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sheets("Sheet1").cells(1,1,).currentregion, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=activesheet.cells(3,1), TableName:=sPivotName, DefaultVersion _
:=xlPivotTableVersion12

nutsch

Your initial code was trying to recreate Pivottable58 on sheet4 (which causes issues).
I'm trying to replace the hardcoded destinations with more flexible ones so your code works every time.

appc

ASKER

This:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sheets("Sheet1").cells(1,1,).currentregion, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=activesheet.cells(3,1), TableName:=sPivotName, DefaultVersion _
:=xlPivotTableVersion12

does not change anything.

nutsch

Still error on cells(1,1,)

Here is a tested version:

Sub Macro1()
'
' Macro1 Macro
'
Dim sPivotName As String, pt As PivotTable

sPivotName = "Pivot_" & Format(Now(), "ddmm_hhmmss")
'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Sheet1").Cells(1, 1).CurrentRegion, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(3, 1), TableName:=sPivotName, DefaultVersion _
        :=xlPivotTableVersion12

Set pt = ActiveSheet.PivotTables(sPivotName)
    With pt.PivotFields("Original_Portfolio")
        .Orientation = xlColumnField
        .Position = 1
    End With
    pt.AddDataField pt.PivotFields("NPV"), "Sum of NPV", xlSum
End Sub

Open in new window

Thomas

appc

ASKER

It stopped and said:

error 400

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.