1004 problem with ActiveWorkbook.PivotCaches.Add

[Jim Snyder]

I am trying to build a macro to be placed in a blank spreadsheet for use as a template. My development platform is Excel 2003 on Windows XP SP2. The script was initially recorded as a macro against a single data file with absolute range references and worked both on my develpment platform and on the production platform (Excel 2000 on Windows 2000). However, I have been having fits trying to convert it to dynamic addressing for the PivotCaches.Add. I develop Excel solutions a few times a year and this pivot table is the deepest I have dug into OLE code, so I am not an expert and could have a simple problem. I included the entire macro because I am unsure if there are sideeffects to what some of it is doing. I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

Since I do not get this error with the absolute references, I am puzzled as to what to fix. That is another reason for supplying the entire macro:

Sub PivotMacro()
'
' PivotMacro Macro
' Macro recorded 7/14/2009 by Jim Snyder
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\temp-16000000\TestFile.txt", _
Destination:=Range("A1"))
.FillAdjacentFormulas = True
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "~"
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 2)
.Refresh BackgroundQuery:=False
End With

' Variables for determining current range
Dim DataRows As Long
Dim DataColumns As Long
' Dim PivotTableRange As Range
Dim PivotTableRange As String
Range("A1").Select
Selection.End(xlDown).Select
DataRows = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
DataColumns = ActiveCell.Column
' PivotTableRange = "Sheet1!R1C1:R" & Format(DataRows) & "C" & Format(DataColumns)
' PivotTableRange = ("Sheet1").Range("A1").CurrentRegion.Address
PivotTableRange = ActiveSheet.Range("A1").CurrentRegion.Address

Selection.EntireRow.Insert
Range("A1:O1").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "Check #"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("B1").Select
ActiveCell.FormulaR1C1 = "Check Date"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "EOB #"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("D1").Select
ActiveCell.FormulaR1C1 = "From Date"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("E1").Select
ActiveCell.FormulaR1C1 = "To Date"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Type"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Participant"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "BPA Status"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("I1").Select
ActiveCell.FormulaR1C1 = "Type Code"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("J1").Select
ActiveCell.FormulaR1C1 = "Plan"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("K1").Select
ActiveCell.FormulaR1C1 = "Member"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("L1").Select
ActiveCell.FormulaR1C1 = "Patient"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("M1").Select
ActiveCell.FormulaR1C1 = "Payee"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("N1").Select
ActiveCell.FormulaR1C1 = "Check Amount"
With ActiveCell.Characters(Start:=1, Length:=12).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("O1").Select
ActiveCell.FormulaR1C1 = "Br #"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Columns("G:G").Select
Selection.ColumnWidth = 12.14
Columns("H:H").ColumnWidth = 7.71
Columns("I:I").ColumnWidth = 7.43
Columns("N:N").ColumnWidth = 9.86
Range("O2").Select
Range([a1].CurrentRegion.Address).Sort Key1:=Range("O2"), Order1:=xlAscending, Key2:= _
Range("H2"), Order2:=xlAscending, Key3:=Range("J2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
PivotTableRange).CreatePivotTable TableDestination:="", TableName:= _
"SumPivotTable"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("SumPivotTable").PivotFields("Br #")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("SumPivotTable").PivotFields("BPA Status")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("SumPivotTable").PivotFields("Type Code")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("SumPivotTable").PivotFields("Plan")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("SumPivotTable").PivotFields("Check Amount")
.Orientation = xlDataField
End With
Range("C6").Select
Selection.Delete
Range("B6").Select
Selection.Delete
Sheets("Sheet1").Select
Range([a1].CurrentRegion.Address).Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:A").ColumnWidth = 10
Columns("B:B").ColumnWidth = 10
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 11
Columns("E:E").ColumnWidth = 9
Columns("F:F").ColumnWidth = 8
Columns("G:G").ColumnWidth = 12
Columns("H:H").ColumnWidth = 8
Columns("I:I").ColumnWidth = 8
Columns("J:J").ColumnWidth = 8
Columns("K:K").ColumnWidth = 26
Columns("L:L").ColumnWidth = 26
Columns("M:M").ColumnWidth = 40
Columns("N:N").ColumnWidth = 10
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("M1").Select
Selection.Copy
Range("N1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Concatenated Columns"
With ActiveCell.Characters(Start:=1, Length:=20).Font
.Name = "Century Gothic"
.FontStyle = "Bold"
.Size = 11
End With
Range("N2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("N2:N24"), Type:=xlFillDefault
Range("N2:N24").Select
Selection.NumberFormat = "General"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]&RC[2]"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:" & DataRows), Type:=xlFillDefault
Range("N2:" & DataRows).Select
Range("O2").Select
Selection.Subtotal GroupBy:=14, Function:=xlSum, TotalList:=Array(15), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ChDir "C:\temp-16000000"
ActiveWorkbook.SaveAs Filename:= _
"C:\temp-16000000\TestFile.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[xenou]

Time to learn debugging skills ...

http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

I'd recommend you step through your code until you get to the line that hangs, then inspect the parameters being used for the function there (destination range, source range, etc.). Your answer might come from simply:

1) Typing in the immediate window:

?PivotTableRange.Address

2) checking that range address on the worksheet and seeing what's there.

I don't use characters like # signs in column names as a rule, but I'll assume that's okay for pivot tables. For what its worth, I find pivot table code to be too long and awkward for my tastes - if possible, your template should have as much in it as you can build by hand rather than having to create everything in code - you may only need to refresh the data, rather than build the whole thing from scratch. However, I'll assume there's a good reason why you haven't done that so my best advice is, as stated above, is to start using your debugging tools - step throughs, breakpoints, watches, mouseovers, the immediate window, and debug.print statements are all part of your arsenal on that front.

 

[Jim Snyder]

Thanks for the reply. I know exactly where the error is happening but do not know a workaround. When I swap out the absolute address for any kind of relative address (string or range) in the ActiveWorkbook.PivotCaches.Add I get the error.

I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

 

[RoryA]

What are the values of DataRows and DataColumns when your code runs?
Incidentally, you would probably get more people looking if you wrapped the code in code tags - it's quite hard to read otherwise, especially when there's a lot of it!

 

[Jim Snyder]

DataRows shows the number of rows in the the active region and DataColumns shows the number of columns.

How do I use code tags? I haven't used them before and didn't see any obvious labeling.

 

[RoryA]

See this page for code tag usage.
What happens if you change this bit of code:

DataRows = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
DataColumns = ActiveCell.Column
' PivotTableRange = "Sheet1!R1C1:R" & Format(DataRows) & "C" & Format(DataColumns)
' PivotTableRange = ("Sheet1").Range("A1").CurrentRegion.Address
PivotTableRange = ActiveSheet.Range("A1").CurrentRegion.Address

to this:

DataRows = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
DataColumns = ActiveCell.Column
msgbox "Rows: " & DataRows 7 ", columns: " & DataColumns
PivotTableRange = "Sheet1!R1C1:R" & DataRows & "C" & DataColumns

also, I assume the data is actually on Sheet1?

 

[Jim Snyder]

I get a syntax error on the row:
msgbox "Rows: " & DataRows 7 ", columns: " & DataColumns

Data actually exists on sheet1.

Working on the syntax...

 

[RoryA]

Sorry that's a typo. Change the 7 to &

 

[Jim Snyder]

I get the message box with both values. At the end of the script I use each value to do some column manipulation. If I put the relative paths in, that part works fine.

 

[Jim Snyder]

I tried to used a named range, but although that works first run, the named range is now part of the template and each subsequent run fails.