Would the amount of source data mess with my VBA macro?

[punter]

Thank you very much in advance for your help.

I am having an excel VBA issue and it is driving me crazy. The code is below:

Sub ReceivingSummaryPivotTable()

Dim pt As PivotTable
Dim cacheOfPt As PivotCache 'this is the source data of the pt
Dim pf As PivotField
Dim pi As PivotItem


On Error Resume Next
Sheets("Receiving Summary").Select
ActiveSheet.pivottbales("ReceivingSummary").TableRange2.Clear 'deletes the PT

'set the cache of the PT

Sheets("Receiving").Select
Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("ReceivingSourceData"))

'create the PT
Sheets("Receiving Summary").Select
Set pt = ActiveSheet.PivotTables.Add(cacheOfPt, Range("a1"), "ReceivingSummary")


'put the fields in

With pt

'add the fields

.PivotFields("Receive Site No").Orientation = xlRowField
.PivotFields("Receive Units SUM").Orientation = xlDataField
.PivotFields("Receive Amt SUM1").Orientation = xlDataField

'set the number format

Columns("B:B").NumberFormat = "#,##0"

Columns("C:C").NumberFormat = "#,##0.00"



'go to classic view

.RowAxisLayout xlTabularRow

On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt

End With

End Sub

I know the code works because I have used it dozens of times. I know the range works because I have used that dozens of times. The only variable is that instead of fifty to sixty thousand lines of data I am trying to run it on 170,000.

Any thoughts? Thank you so very much.

Eddie

 

[steve the fish]

This looks wrong!

ActiveSheet.pivottbales("ReceivingSummary").TableRange2.Clear 'deletes the PT

 

[punter]

Thanks for the quick reply.

I assume you are referring to the mispelling of pivottables. When I changed it correctly it still does not seem to work.

 

[steve the fish]

Try removing the on error and see where it bugs out.

 

[ParamRay]

Try removing the on error and see where it bugs out.

I agree with Steve the Fish. Avoid using On Error statements unless you're intentionally bypassing a known error. Take it out and see where the error occurs...

 

[punter]

It is breaking on this line:

ActiveSheet.PivotTables("Receivingsummary").TableRange2.Clear 'deletes the PT

It looks okay to me. Obviously something is wrong.

 

[steve the fish]

So presumably there isn't a pivot called receivingsummary on the activesheet. But you are going to create it again so carry on running through the code. Where does I break now?

 

[ParamRay]

.
.

Try this instead:

With ActiveSheet
    .Range(.PivotTables("ReceivingSummary").TableRange2.Address).Clear
End With

 

[punter]

I think I got it. I need change this:

Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("ReceivingSourceData"))

To this:

Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, "ReceivingSourceData")

It seems to be working great.

Your thoughts are very welcome but I think I have the fix that I need.

Thank you so very much for your replies.

Eddie