Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build Pivot Table with VBA; Keeps Throwing an Error
I keep getting Run-Time Error 5
Invalid Procedure call or argument The following is hilighted yellow: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion) .CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 I can't figure out what is causing the error... All data resides on this sheet: C2_UnionQuery I am trying to put the Pivot Table on this sheet: Summary My code is below: Sub BuildPT() Application.ScreenUpdating = False Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = "Summary" Then Sheets("Summary").Delete Set ws = Worksheets.Add 'ws.Move After:=Sheets(Sheets.Count) ws.Name = "Summary" End If Next wks Application.ScreenUpdating = False Sheets("C2_UnionQuery").Select Range("A1").Select 'Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion) .CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Sheets("Summary").Select With ActiveSheet.PivotTables("PivotTable2").PivotFields ("RVP") .Orientation = xlRowField .Position = 1 End With '...some more Pivot Fields here... End Sub Please help...... Thanks, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build Pivot Table with VBA; Keeps Throwing an Error
Couldn't figure it out, but when I restarted, and thus cleared the memory, it
worked........ -- RyGuy "ryguy7272" wrote: I keep getting Run-Time Error 5 Invalid Procedure call or argument The following is hilighted yellow: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion) .CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 I can't figure out what is causing the error... All data resides on this sheet: C2_UnionQuery I am trying to put the Pivot Table on this sheet: Summary My code is below: Sub BuildPT() Application.ScreenUpdating = False Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = "Summary" Then Sheets("Summary").Delete Set ws = Worksheets.Add 'ws.Move After:=Sheets(Sheets.Count) ws.Name = "Summary" End If Next wks Application.ScreenUpdating = False Sheets("C2_UnionQuery").Select Range("A1").Select 'Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("C2_UnionQuery").Range("A1").CurrentRegion) .CreatePivotTable _ TableDestination:="[C2_UnionQuery.xls]Summary!R3C1", TableName:="PivotTable2", _ DefaultVersion:=xlPivotTableVersion10 Sheets("Summary").Select With ActiveSheet.PivotTables("PivotTable2").PivotFields ("RVP") .Orientation = xlRowField .Position = 1 End With '...some more Pivot Fields here... End Sub Please help...... Thanks, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different ways to build Pivot table | Excel Discussion (Misc queries) | |||
build a pivot table from multiple other pivot tables. | Excel Discussion (Misc queries) | |||
pivot table build using excel macro | Excel Programming | |||
How can I build a pivot table from multiple worksheets which are . | Excel Discussion (Misc queries) | |||
How do you build pivot table in VB | Excel Programming |