title: Excel VBA - Stuck on generating pivot table from variable data range
body: <p>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. </p>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If anyone sees what's wrong with it, it would be much appreciated.</p>