title: PivotTable error 1004
body: <p>Can anyone see why this code would cause a 1004 error on the last line? Everything works great up until that last line. I had it working, then it started getting this error and I can't figure out why. Sheet2 is a blank sheet. Sheet1 is currently just test data, 10 rows, 3 columns. It starts at B3. Anyone have any ideas?</p>
  
  <pre><code>    Sub CreatePivot()
          ' Define RngTarget and RngSource as Range type variables
          Dim RngTarget As Range
          Dim RngSource As Range
          Dim intLastCol As Integer
          Dim intCntrCol As Integer
  
          ' RngTarget is where the PivotTable will be created (ie: Sheet2, Cell B3)
          Set RngTarget = ThisWorkbook.Worksheets("Sheet2").Range("B3")
  
          ' RngSource defines the Range that will be used to create the PivotTable
          ' ActiveWorkbook = The currently opened Workbook
          ' ActiveSheet = The currectly opened sheet
          ' UsedRange = The Range of cells with active data in them
          Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange
  
          ' Select the Range
          RngSource.Select
  
          ' Copy the Range into the clipboard
          RngSource.Copy
  
          ' Create a new PivotTable using the RngSource defined above,
          ' in Excel format,
          ' placed at the RngTarget location,
          ' And name it PivotB3 just for reference if needed
          ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource).CreatePivotTable RngTarget, "PivotB3"
  
          ' Get the last used column from the data table
          intLastCol = RngSource.Columns(RngSource.Columns.Count).Column
  
          ' Select the Pivot table so we can apply the conditional formats
          ActiveSheet.PivotTables("PivotB3").PivotSelect "", xlDataAndLabel, True
  </code></pre>