Pivot Table Macro - Reference Not Found

[allstarrunner]

Hello and thank you in advance for your help! I have tried to figure this one out, but I am at a loss. I am trying to make a simple Pivot Table from Column E which will open a new sheet and then show two columns in the pivot, the first column (Column A on the new spreadsheet) which has the names from column E and then Column B (on the new Spreadsheet) which shows the count of the names from column E. But I keep getting a "Reference not found Error" (Shown in Bold) The name of the sheet that contains Column E is "Data Dump". I have Windows 7 and use Excel 2007.
Here is the code I have:

 ' Name the Range and Create Pivot Table
    ActiveWorkbook.Names.Add Name:="DataDumpPivot", RefersToR1C1:= _
        "=OFFSET(DataDump!R1C5,0,0,COUNTA(DataDump!C5),1)"
    ActiveWorkbook.Names("DataDumpPivot").Comment = ""
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DataDumpPivot", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion12
    ActiveSheet.Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "Dept Name and Dept Num")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Dept Name and Dept Num"), _
        "Count of Dept Name and Dept Num", xlCount
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "Dept Name and Dept Num")
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("B6").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Dept Name and Dept Num"). _
        AutoSort xlDescending, "Count of Dept Name and Dept Num", ActiveSheet. _
        PivotTables("PivotTable4").PivotColumnAxis.PivotLines(1), 1

 

[Jerry Sullivan]

Hi allstarrunner,

That part of the code worked okay for me.

What statement is it actually highlighting in the Debugger?
The code that you have in bold font is 3 statements and the Debugger should only highlight one.

 

[PCRIDE]

Could be the PivotTable4

Sometimes when running a macro from a new sheet it starts at PivotTable1

Is there a command to make a pivot table with a default name?

 

[Jerry Sullivan]

Hi PCRIDE,

The code that allstarrunner is using is trying to use PivotTable4 as its name.

The code could be improved by either checking that PivotTable4 does not already exist, or using TableName:="" which will cause Excel to generate a name that is not in use.

I don't think that's the cause of the OP's problem though, because that would return a different error message than "Reference not found".

More likely the Sheet Name "DataDump" or added Dynamic Named Range "DataDumpPivot" isn't being found.

When allstarrunner clarifies which statement the debugger is stopping at, it will be easier to check which reference is not being found.

 

[Jerry Sullivan]

One other possibilty....

allstarrunner, do you have any Event Procedures in your Workbook or Worksheet such as Worksheet_SelectionChange?

 

[allstarrunner]

Hello All and thank you for helping me out.
I went in and changed the TableName:="" in place of what was there, TableName:="PivotTable4". But, I got the same error. (Reference not found).
This is what the debugger highlighted:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DataDumpPivot", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="", TableName:="", _
DefaultVersion:=xlPivotTableVersion12

I also created a new workbook, renamed the first sheet "DataDump", put data in Column E to try the code in it's own Macro; I then got a different error, "Unable to get the PivotTable property of the Worksheet class." and the debugger highlighted this:

With ActiveSheet.PivotTables("PivotTable4").PivotFields( _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
"Dept Name and Dept Num")<o:p></o:p>

I assume it has something to do with "PivotTable4", but I'm not sure how to fix it.

Just in case it helps, here is the full code for my macro (and I apologize for the horrific and unefficient code you are probably about to lay your eyes on - I hope it doesn't scar you too bad):

'Create Column E
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
'Concatenate C and D
Range("E1:E" & Range("C" & Rows.Count).End(xlUp).Row) = "=C1&"" "" & D1"
 
'
'Divide to get DJIT Price
Range("L2:L" & Range("O" & Rows.Count).End(xlUp).Row) = "=O2/S2"
'
'Clear Number_Empty and Paste Values
'
Cells.Select
Selection.Replace What:="#Empty", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Application.Run "PERSONAL.XLSB!Paste_Values"
'
 
' Name the Range and Create Pivot Table
ActiveWorkbook.Names.Add Name:="DataDumpPivot", RefersToR1C1:= _
"=OFFSET(DataDump!R1C5,0,0,COUNTA(DataDump!C5),1)"
ActiveWorkbook.Names("DataDumpPivot").Comment = ""
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DataDumpPivot", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="", TableName:="", _
DefaultVersion:=xlPivotTableVersion12
ActiveSheet.Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Dept Name and Dept Num")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Dept Name and Dept Num"), _
"Count of Dept Name and Dept Num", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Dept Name and Dept Num")
.Orientation = xlRowField
.Position = 1
End With
Range("B6").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Dept Name and Dept Num"). _
AutoSort xlDescending, "Count of Dept Name and Dept Num", ActiveSheet. _
PivotTables("PivotTable4").PivotColumnAxis.PivotLines(1), 1
 
'Format the Pivot
Columns("A:B").Select
Selection.Copy
Application.Run "PERSONAL.XLSB!Paste_Values"
Columns("A:B").EntireColumn.AutoFit
Columns("A:B").EntireColumn.AutoFit
Selection.ColumnWidth = 9.71
Application.CutCopyMode = False
Range("C4").Select
Columns("B:B").ColumnWidth = 14.14
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
ActiveCell.FormulaR1C1 = "Department Name and Number"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Qtr Lines"
Range("C1").Select
ActiveCell.FormulaR1C1 = "% to Total"
 
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LR + 1).Formula = "=SUM(B1:B" & LR & ")"
With Range("C1:C" & LR)
.Formula = "=B1/B$" & LR + 1
.Value = .Value
End With
'Range("C2").Select
'ActiveCell.FormulaR1C1 = "=RC[-1]/R683C2"
'Range("C2").Select
'Selection.AutoFill Destination:=Range("C2:C683")
Range("C2:C683").Select
Columns("C:C").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
Range("A1:C1").Select
Range("C1").Activate
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Thank you!!

 

[PCRIDE]

Try PivotTable1

I did have a problem like this before when a macro was trying to create a sheet which didn't exist, i.e Sheet 1, 2, 3 etc.. Since I had recorded the macro with a workbook that had a "Sheet4" on a new file it failed because it never existed.

I still think its the code calling for something that is not there. I didn't see in the code where its creating a pivot table with a defined name.

I just recorded a macro creating a pivot table and this is what i get.


So you can see that by default is starts out at Pivot Table1, and also Sheet4 to drop it in since each workbook by default has 3 sheets, the next one would be 4. If I ran this code on a sheet that didn't have Sheet 4 defined, it would probably fail Because the code doesn't Create a new sheet first. Just like the pivot table.

I am not an expert, but is just what I see it doing.

Range("B2:D28").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R2C2:R28C4", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet4").Select

 

[Jerry Sullivan]

Try this modified version of the first half of your code which should be more robust.

Sub TEST1()
    With Sheets("DataDump")
        .Columns("E:E").Insert Shift:=xlToRight, _
            CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("E1:E" & .Range("C" & Rows.Count).End(xlUp).Row) = "=C1&"" "" & D1"
        'Divide to get DJIT Price
        .Range("L2:L" & .Range("O" & Rows.Count).End(xlUp).Row) = "=O2/S2"
    
        With .UsedRange
            .Replace What:="#Empty", Replacement:="", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            .Value = .Value
        End With
    End With

    ' Name the Range and Create Pivot Table
    With ActiveWorkbook
        .Names.Add Name:="DataDumpPivot", RefersToR1C1:= _
            "=OFFSET(DataDump!R1C5,0,0,COUNTA(DataDump!C5),1)"
        .PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="DataDumpPivot", _
            Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="", _
            TableName:="", DefaultVersion:=xlPivotTableVersion12
    End With
    With ActiveSheet.PivotTables(1)
        With .PivotFields("Dept Name and Dept Num")
            .Orientation = xlRowField
            .Position = 1
        End With
        .AddDataField .PivotFields("Dept Name and Dept Num"), _
            "Count of Dept Name and Dept Num", xlCount
        With .PivotFields("Dept Name and Dept Num")
            .Orientation = xlRowField
            .Position = 1
            .AutoSort xlDescending, "Count of Dept Name and Dept Num", _
                .Parent.PivotColumnAxis.PivotLines(1), 1
        End With
    End With
End Sub