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

appcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
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

0
appcAuthor Commented:
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
0
nutschCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

nutschCommented:
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.
0
appcAuthor Commented:
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.
0
nutschCommented:
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
0
appcAuthor Commented:
It stopped and said:

error 400
0
nutschCommented:
Which line? It works for me in the attached file.

Thomas
Book5.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.