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!
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!