Link to home
Start Free TrialLog in
Avatar of csehz
csehzFlag for Hungary

asked on

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?

thanks,
Sub PivotCreation()

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

'Source
     ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C2").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1"
'Place
    'ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(39, 1)
'or
    'ActiveSheet.PivotTableWizard TableDestination:=Sheet1.Cells(39, 1)
'or
    ActiveSheet.PivotTableWizard TableDestination:=Workbooks("PivotVBATemplate.xls").Worksheets("Sheet2").Cells(9, 1)
'Always
    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

PivotVBATemplate.xls
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of csehz

ASKER

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