Build Pivot Table with VBA; Keeps Throwing an Error

R

ryguy7272

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

ryguy7272

Couldn't figure it out, but when I restarted, and thus cleared the memory, it
worked........
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top