Trouble creating pivot table in VBA

[jasonfish11]

All,
I've searched for the answer to my question and haven't found much help. I was hoping someone could point out where I'm missing something as the below coding fails at the last line and never creates a pivot table.

'Create pivot tables
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String


Worksheets("Combined").Select
SrcData = ActiveSheet.Name & "!" & Range("A1:E" & comLR2).Address(ReferenceStyle:=xlR1C1)


Worksheets("Recon Pivot").Select
StartPvt = ActiveSheet.Name & "!" & Range("A2").Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")

The variables of SrcData and StartPvt both appear correctly when I hover over them in VBA.
Any help would be much appreciated.

 

[mart37]

you used ranges instead of strings

 

[mart37]

comLR2????

 

[krrishkrsna]

try below code, its very simple

Sub t()


'xlSum           -4157
'xlAverage      -4106
'xlCount          -4112
'xlMax            -4136
'xlMin             -4139


Dim shtrng As String, x As Integer, lblcolpos As Integer, lblcolval As String, lblrowpos As Integer, lblrowval As String, lbldatapos As Integer, lbldataval As String, lbldataval1 As String


'sheet Data contains actual data, which you used to create pivot table
shtrng = Sheets("Data").Name & "!" & Sheets("Data").UsedRange.Address


Sheets.Add.Name = "Pivot"


ActiveWorkbook.PivotCaches.Create(xlDatabase, shtrng).CreatePivotTable TableDestination:="Pivot!R5C2", TableName:="PivotTable1"


'create sheet Labels and place the field names as per below (below are example)
'row 1 contains heading as Row, Column, Data, and Function. below that field names will be mentioned
Row	Column	Data	Function
field1	field1	field1	-4157	'xlsum
field2	field2	field2	-4136	'xlmax


lblcolpos = 1
For x = 2 To Sheets("Labels").Cells(Rows.Count, 2).End(xlUp).Row
    lblcolval = Sheets("Labels").Cells(x, 2)
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(lblcolval)
            .Orientation = xlColumnField
            .Position = lblcolpos
        End With
    lblcolpos = lblcolpos + 1
Next x


lblrowpos = 1
For x = 2 To Sheets("Labels").Cells(Rows.Count, 1).End(xlUp).Row
    lblrowval = Sheets("Labels").Cells(x, 1)
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(lblrowval)
            .Orientation = xlRowField
            .Position = lblrowpos
        End With
    lblrowpos = lblrowpos + 1
Next x


lbldatapos = 1
For x = 2 To Sheets("Labels").Cells(Rows.Count, 3).End(xlUp).Row
    lbldataval = Sheets("Labels").Cells(x, 3)
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(lbldataval)
            .Orientation = xlDataField
            .Function = Sheets("Labels").Cells(x, 4).Value
            .NumberFormat = "$#,##0.00"
            .Position = lbldatapos
        End With
    lbldatapos = lbldatapos + 1
Next x


'removing subtotals
Dim pt As PivotTable, pf As PivotField


On Error Resume Next


For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields


pf.Subtotals(1) = True
pf.Subtotals(1) = False


Next pf
Next pt

 

[jasonfish11]

Sorry comLR2 is a variable set up earlier in the macro to find the last row in the "Combined" tab.

 

[jasonfish11]

you used ranges instead of strings

Sorry what do you mean?

The result of the StartPvt and SrcData are "Recon Pivot!R2C1" and "Combined!R1C1:R308C5" respectfully.

Both of those results are what I would expect.

 

[mart37]

startpvt is wrong
name with space: use '

 

[jasonfish11]

startpvt is wrong
name with space: use '

Thank you but this did not help. I also tried removing the space all together to make the worksheet name "ReconPivot" and it's still failing.

 

[mart37]

what is the error?
there are no pivottables on reconpivot?

 

[RoryA]

Try this:

'Create pivot tables
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As Range
Dim SrcData As String


Worksheets("Combined").Select
SrcData = ActiveSheet.Name & "!" & Range("A1:E" & comLR2).Address(ReferenceStyle:=xlR1C1)


Worksheets("Recon Pivot").Select
Set StartPvt = Worksheets("Recon Pivot").Range("A2")
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")