title: Macro Select all for Pivot tables
body: <p>I am trying to make a macro for creating a pivot table. I need the Macro to select all of the data on the sheet; a friend of mine has helped me create the code above, however the macro only selects the first two columns of data instead of all 48 columns. Does anyone see any mistake we may have made? Any response would be greatly appreciated! There will always be 48 columns however there number of rows will vary from week to week. </p>
  
  <pre><code>Dim lastRow As Long
  Dim lastCol As Long
  
  lastRow = 1
  lastCol = 1
  
      While Sheets("Sheet1").Cells(lastRow, lastCol).Value &lt;&gt; ""
          lastCol = lastCol + 1
      Wend
  
  lastCol = lastCol - 1
  
      While Sheets("Sheet1").Cells(lastRow, 1).Value &lt;&gt; ""
          lastRow = lastRow + 1
      Wend
  
  lastRow = lastRow - 1
  
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      "Sheet1!R1C1:R" &amp; lastRow &amp; "C" &amp; lastCol, Version:=xlPivotTableVersion12).CreatePivotTable _
      TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
      xlPivotTableVersion12
  
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  </code></pre>