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

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