PivotTable error 1004

Question

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?

    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

score 1 · Accepted Answer · edited May 23 '17 at 12:13

You are getting the error because the pivot table is on sheet2 and not the activesheet. You could fix the error by simply selecting sheet2 prior to selecting the pivot table, but what you really want to do is eliminate all selects from your code. See Avoid Using Select for further explanation/examples.

Try this:

Sub CreatePivot()
        Dim RngTarget As Range
        Dim RngSource As Range
        Dim ws As Worksheet
        Dim pt As PivotTable

        Set ws = ThisWorkbook.Sheets("Sheet2")
        ws.Cells.Clear
        ' RngTarget is where the PivotTable will be created (ie: Sheet2, Cell B3)
        Set RngTarget = ws.Range("B3")

        Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange

        ' Create a new PivotTable
        ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource).CreatePivotTable _
            RngTarget, "PivotB3"
        Set pt = RngTarget.PivotTable

        ' We now have access to the pivot table and can modify as needed
        pt.PivotSelect "", xlDataAndLabel, True
        'ActiveSheet.PivotTables("PivotB3").PivotSelect "", xlDataAndLabel, True
End Sub

NOTE: I've removed variables and comments that aren't part of your issue to make it easier to see what is happening.

Thank you! That error is gone. Now I've got another 1004 though, just a few lines past the previous. I'm sure it's probably something similar. Watch for my next post. :) — user2021539, May 02 '13 at 12:10

score 0 · Answer 2 · answered May 01 '13 at 03:32

Try below 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 = 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
    Dim oPC As PivotCache
   Set oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource)

   Dim oPT As PivotTable
   Set oPT = oPC.CreatePivotTable(RngTarget, "PivotB3", True)

    ' 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
    'Worksheets("Sheet2").PivotTables("PivotB3").PivotSelect "", xlDataAndLabel, True
End Sub

PivotTable error 1004

2 Answers2