VBA Excel - Pivot creation

Dear Experts,

Could you please have a look at the attached file, on Sheet1 it contains a simple table and based on that trying to do a pivot.

I have two problems with it
1) somehow the macro always add a new sheet to the file, but I am not sure why
2) in the current format getting error message Run-time error 1004, Unable to get the PivotFields property of the PivotTable class at row <<    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty") >>

Could you advise what causes these in Module1 macro?

Sub PivotCreation()

'Count actual base sheet rows
Dim LastRowWithValue As Long
LastRowWithValue = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

     ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C2").CreatePivotTable TableDestination:="", _
    'ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(39, 1)
    'ActiveSheet.PivotTableWizard TableDestination:=Sheet1.Cells(39, 1)
    ActiveSheet.PivotTableWizard TableDestination:=Workbooks("PivotVBATemplate.xls").Worksheets("Sheet2").Cells(9, 1)
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
'Column, Row fields
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
        "Item", ColumnFields:="Area"
'Data fields
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty")
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
        .Function = xlSum
    End With
'Formatting, sum/count
'    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
'       "Count of Qty").Function = xlSum
'Certain columns not visible
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .PivotItems("West").Visible = False
    End With
'Columns replace and order
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems( _
        "South").Position = 1
'Paste special as values

End Sub

Open in new window

csehzIT consultantAsked:
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Your problems are both in lines 8-10. The C2 in the SourceData argument refers to column 2 (i.e. column B), so you are not including column C in the data, so you can't refer to Qty. Leaving the TableDestination argument blank means that Excel will always put it on a new sheet. You didn't say where you want it, so we can't fix it.
csehzIT consultantAuthor Commented:
Rory thanks I understand, so the error message problem can easily solve with including column C so applying C3.

And relating the additional sheet, better to define at that 8-10 lines at remove the 16
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.

All Courses

From novice to tech pro — start learning today.