I keep getting Run-Time Error 5
Invalid Procedure call or argument

The following is hilighted yellow:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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:=xlDatabase, 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

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:=xlDatabase, 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:=xlDatabase, 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