title: Excel VBA - Generalize Pivot Source Data / Range
body: <p>I recorded the pivot table macro and I'm trying to generalize source data instead of going off of sheet name "REPORTS" </p>
  
  <p>It grabs all the data from active sheet despite what the name of the sheet.</p>
  
  <p><strong>This way I can use the macro to create a pivot table for any active sheet</strong>:-</p>
  
  <pre><code>Sheets("**REPORTS**").Select
  Range("A1").Select
  Sheets.Add.Name = "Pivot"
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("**REPORTS**").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15).CreatePivotTable _
      TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
      :=xlPivotTableVersion15
  Sheets("Pivot").Select
  Cells(3, 1).Select
  With ActiveSheet.PivotTables("PivotTable1")
      .InGridDropZones = True
      .RowAxisLayout xlTabularRow
  End With
  </code></pre>