VBA Pivot Table Creation

[jaxisdex]

Hi guys, I am new to VBA. Like very very new and plan on enrolling in a class within a month. Until then, I have been working on this but it keeps informing me of Compile error: Named argument not found.

this is my code

Public Sub Set_Up_All()


Dim pvttbl As PivotTable
Dim wsdata As Worksheet
Dim mgdata As Range
Dim pvttblcache As PivotCache
Dim wspvttbl As Worksheet
Dim pvtfld As PivotField


Set wsdata = Worksheets("Sheet1")
Set wsdata = Worksheets("Sheet2")
Set mgdata = wsdata.Range("A:J")


Sheets.Add
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=mgdata, Version:=x1PivotTableVersion12).createpivotable pivottablerange:=wspvttbl.Range("A1"), tablename:="PivotTable1"


Set pvttble = wspvttbl.PivotTables("PivotTable1")


Set pvtfld = pvttbl.PivotFields("User ID")
pvtfld.Orientation = xlRowField
pvtfld.Position = 1


Set pvtfld = pvttbl.PivotFields("Transaction ID")
pvtfld.Orientation = xlRowField
pvtfld.Position = 2


Set pvtfld = pvttbl.PivotFields("Difference in Mins")
pvtfld.Orientation = xlRowField
pvtfld.Position = 3


Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields("User ID").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
Worksheets("Sheets2").PivotTables("PivotTable1").PivotFields("Transaction ID").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False)
Worksheets("Sheets2").PivotTables("PivotTable1").PivotFields("Difference in Mins"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Columns("B:B").EntireColumn.AutoFit
Worksheets("Sheet2").PivotTables("PivotTable1").PivotSelect "'Difference in Mins'[All]" _
, xlLabelOnly, True
Range("K13").Select
Worksheets("Sheets2").PivotTables("PivotTable1").PivotFields("Difference in Mins"). _
PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:="0.00", Value2:= _
"3.00"


pvttble.ManualUpdate = True


End Sub


can someone please help me? is there something wrong at the 'Version:=x1PivotTableVersion12' part?

im trying to build a pivot that will created for the data im using. i have to pull over 50000 lines of data every day and this would save me some time to not having to create it. I would really appreciate any and all help, please and thank you! this i got from a combination of trying to record it, realizing too late that pivot table recorded macros wont work in Excel 2007, and using online resources.

Thanks, Jaxs

 

[pbornemeier]

There are errors in your code. Do the following:

1) In the VBA Editor Tools | Options dialog uncheck "Auto Syntax Check" and check "Require Variable Declaration"
2) Turn on the macro recorder and create your PivotTable, the code used to create the PT will be captured
3) Edit the code to make it more generic

For example, in this line:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=mgdata, Version:=x1PivotTableVersion12).createpivotable pivottablerange:=wspvttbl.Range("A1"), tablename:="PivotTable1"

The second character of x1PivotTableVersion12 is a lowercase L not a numeric 1
PivotCaches.Add is not valid use PivotCaches.Create
createpivotable should be CreatePivotTable
pivottablerange should be TableDestination

Another problem, not in that line:
You have declared pvttbl, but in several places use pvttble

Here is some code recorded when a PivotTable was created:

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C1:R37C11", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet7!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet7").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("B_CAT")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("J_#"), "Sum of J_#", xlSum

This edited code will create a PivotTable from data starting in cell A1 on the "Working" worksheet and place it in A3 of the "PT" worksheet

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Working!" & worksheets("Working").Range("A1").CurrentRegion.Address(True, True, xlR1C1), _
        Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="PT!R3C1", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion14

 

[jaxisdex]

Morning pbornemeier,


Thank you for all your help!!! It took me this weekend to figure out how to modify that code to suit my needs but it ended up working perfectly! Saving me a ton of time.

Thanks,
Jax

Ps. You rock!