PDA

View Full Version : [SOLVED] PivotTable Error



tkaplan
07-15-2015, 01:57 PM
Good afternoon,

I wrote a macro that formats an existing data set, adding formulas, etc. and that part of the macro all works well. when I try to insert a pivot table I am getting an "invalid procedure call on argument" error. I have a macro that does something very similar and I compared the 2 and I can't find a substantial difference so I am really stuck :banghead:. Any help would be appreciated :)


Sub MonthlyBillingMacro()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim numRecs As Long

Set sourcesheet = ActiveSheet
'name sheets
sourcesheet.Name = "Monthly Enrollment"
Set mbrLevelSht = Sheets.Add(after:=Sheets(Sheets.Count))
mbrLevelSht.Name = "Member Level"

'name ranges and insert additional columns
With sourcesheet
numRecs = .Cells(.Rows.Count, "A").End(xlUp).Row - 6
.Columns("O:O").Insert Shift:=xlToRight
.Columns("R:R").Insert Shift:=xlToRight
.Range("A6:X" & numRecs + 6).Name = "AllData"
.Range("O7:Q" & numRecs + 6).Name = "TierLookup"
.Range("N6").Value = "Line of Coverage"
.Range("O6").Value = "Lookup Field"
.Range("Q6").Value = "Initial Tier"
.Range("R6").Value = "Tier"
.Range("N7").Resize(numRecs).FormulaR1C1 = "=VLOOKUP(Trim(RC[-1]),'Macro.xlsm'!PlanLookup,2,False)"
.Range("O7").Resize(numRecs).FormulaR1C1 = "=Trim(RC[-6]&RC[1])"
.Range("R7").Resize(numRecs).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(TRIM(RC[-9]&""I""),TierLookup,3,FALSE)),RC[-1],VLOOKUP(TRIM(RC[-9]&""I""),TierLookup,3,FALSE))"

End With

'pivot table
Dim pt As PivotTable
Application.CutCopyMode = False

'*****THIS NEXT LINE IS WHERE I GET THE ERROR**************
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"AllData", Version:=xlPivotTableVersion10).CreatePivotTable TableDestination _
:="Member Level!R1C1", TableName:="PivotTable", DefaultVersion:= _
xlPivotTableVersion10

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Branch Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Subscriber Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Tier")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Line of Coverage")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Line of Coverage").Caption _
= "Plan"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Plan").AutoSort _
xlDescending, "Plan"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount Due"), "Sum of Amount Due", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount Due")
.NumberFormat = "$#,##0.00"
End With
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium9"

'Format for print
Columns("A:C").EntireColumn.AutoFit
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.CenterHeader = "&12&F"
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True

Application.ScreenUpdating = True
Application.Calculate
Application.Calculation = xlCalculationAutomatic

With sourcesheet
.Select
.Range("A7").Select
ActiveWindow.FreezePanes = True
.Cells.EntireColumn.AutoFit
.Rows("6:6").AutoFilter
End With

MsgBox ("DONE")

End Sub


Thank you!

p45cal
07-16-2015, 01:07 AM
It is very likely that within that line:
"Member Level!R1C1"
should be:
"'Member Level'!R1C1"
(the addition of 2 single quote marks around the sheet name which has a space in it).

tkaplan
07-16-2015, 05:37 AM
THANK YOU!!! That was it :)