title: Excel VBA Reference SourceData for Creating Pivot Table
body: <p>I need help!</p>
  
  <p>In order to convert a table to a list I am using the following VBA formula. It was created by using record macro and the PivotTable Wizard (not the most elegant solution), but it works.</p>
  
  <pre><code>ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
      Array("PasteSheet!R1C1:R300C200"), Version:=xlPivotTableVersion14). _
      CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
      DefaultVersion:=xlPivotTableVersion14
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  ActiveSheet.Cells(3, 1).Select
  ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems( _
      "Count of Value").Position = 1
  ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Orientation = _
      xlHidden
  ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = _
      xlHidden
  Range("A4").Select
  Selection.ShowDetail = True
  </code></pre>
  
  <p>My issue is I want to be able to set the SourceData to reference a stored variable as the source data range will change each time the macro is run, but I can't get it to work and googled everywhere with no result. My best shot was trying the following.</p>
  
  <pre><code>Dim newRange As Variant
  
  Range("A1").Select
  Range(Selection, Selection.End(xlDown)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  
  Set newRange = Selection
  
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
      newRange, Version:=xlPivotTableVersion14). _
      CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
      DefaultVersion:=xlPivotTableVersion14
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  ActiveSheet.Cells(3, 1).Select
  ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems( _
      "Count of Value").Position = 1
  ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Orientation = _
      xlHidden
  ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = _
      xlHidden
  Range("A4").Select
  Selection.ShowDetail = True
  </code></pre>
  
  <p>Help would be much appreciated!</p>