CODEWITHSUNDEEP
vbaexcelpivot-table
Firefox333
Firefox333

Reputation: 93

Excel VBA - Stuck on generating pivot table from variable data range

I have the code below which theoretically should create a pivot table on a 2nd sheet (which exists) using the data it finds on the 'DATA' sheet. However it always crashes as soon as it reaches the part to create the pivot table.

I originally come from a fixed size and afterwards changed it so it should take all the data on my 'DATA' sheet regardless of whether it's a 2x3 or 58x13 table

Sheets("DATA").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "l"
Range("A1").Select



ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
    Sheets("DATA").Range("A1").CurrentRegion, _
    Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Prior. per user!R1C1", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14



Sheets("Prior. per user").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Priority")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Caller")
    .Orientation = xlRowField
    .Position = 1
End With
Range("D2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Priority").PivotItems( _
    "Medium").Position = 2
Columns("D:D").ColumnWidth = 7.43
Columns("C:C").ColumnWidth = 10
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Number"), "Sum of Number", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Number")
    .Caption = "Count of Number"
    .Function = xlCount
End With

If anyone sees what's wrong with it, it would be much appreciated.

Upvotes: 0

Views: 3412

Answers (1)

Rory
Rory

Reputation: 34045

Your target sheet name has spaces in it so you need to enclose it in single quotes:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
    Sheets("DATA").Range("A1").CurrentRegion, _
    Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="'Prior. per user'!R1C1", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14

Personally, I would also use a variable to refer to the pivot table:

Dim PT As PivotTable

Set PT = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("DATA").Range("A1").CurrentRegion, _
    Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="'Prior. per user'!R1C1", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14)


With PT
    With .PivotFields("Priority")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Caller")
        .Orientation = xlRowField
        .Position = 1
    End With
    .PivotFields("Priority").PivotItems("Medium").Position = 2
    .Parent.Columns("D:D").ColumnWidth = 7.43
    .Parent.Columns("C:C").ColumnWidth = 10
    .AddDataField .PivotFields("Number"), "Count of Number", xlCount
End With

Upvotes: 2

Related Questions