excel 2007 issues with pivot refresh

[simplyPS]

I am working with an application that invokes/uses excel 2007.
Application gets data from Oracle database and populates it into a worksheet. It sorts it based on 4 columns. (code pasted below)
Then, it invokes some macros (vba code) which basically refreshes 2 pivot tables (code is pasted below).
Issue I am running into is when there are 285,000 rows in the worksheet, vba code fails while trying to refresh the pivot table (error text is something like 'Refresh method of pivot table failed'). I also get a popup error message 'excel cannot complete task with available resources. choose less data or close other applications' .....I get this message even when I manually refresh the pivot table.

...However if I save the worksheet (deleting the pivot etc) into a different file as CSV , then delete the data from the old worksheet and import the data from CSV, I am able to manually refresh the pivot tables !

what may be causing the issue with the pivot refresh of the original worksheet and how to resolve it?
Any assistance in this regards is appreciated.

thanks.



Here is the VBA code:


'sort raw data columns in the order Admin Dept number, name , rolename
Set w = ThisWorkbook.Sheets("Raw Data")
w.Select
With w.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("D4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("J4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("K4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range(Range("B4"), Range("B4").SpecialCells(xlLastCell))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
PC.Refresh
Next PC

 

[simplyPS]

I did want to add that if worksheet has fewer rows (say 65,000), then it works fine....issue began to surface when we have 285,000 rows.

 

[Jerry Sullivan]

Does the code fail with 285K rows if you eliminate the sorting step?

Is there a reason that the raw data needs to be sorted? (if the range is only being used as a data source for the Pivot- the Pivot can handle sorting the displayed results).

 

[simplyPS]

Does the code fail with 285K rows if you eliminate the sorting step?

Is there a reason that the raw data needs to be sorted? (if the range is only being used as a data source for the Pivot- the Pivot can handle sorting the displayed results).

It seems to fail even without the sort logic.
It was being sorted as requested by the users.
I am also noticing a different error at times: "Application-Defined or Object-Defined Error"

There are no calculated fields involved. However, there are some "numbers" stored as "text" for 2 columns...I tried formatting those columns as "Text" , but still can't get the pivot to refresh.

There is vba code to copy/paste raw data from different worksheets (in the same workbook) and combining them into one worksheet. The combined raw data is part of the dynamic table, which is being passed as data source for the pivot. Whether I provide a dynamic range (=OFFSET('Raw Data'!$B$3,0,0,COUNTA('Raw Data'!$B:$B)-2,COUNTA('Raw Data'!$1:$1))) or dynamic table as the source for the pivot, it doesn't seem to work for 285K rows, but works for less data.

Here is the code to copy/paste which is called 9 times since there 9 "Raw data" tabs:

' copies from source and appends it to the "Raw data" tab
' due to nvision (application) limitation, the "Raw data" tab can only hold 65,000 rows. to addresses this, we have added multiple raw
' data tabs and finally appending all the data to the "Raw Data" (1st) tab by using this macro

Dim wsSource As Worksheet, wsDest As Worksheet
Dim lastRow As Long

Set wsSource = ThisWorkbook.Sheets(zcuSource)
Set wsDest = ThisWorkbook.Sheets("Raw Data")

If wsSource.Range("B4").Value <> "" Then 'worksheet conatins data

lastRow = wsSource.Range("B" & Rows.Count).End(xlUp).Row
wsSource.Range("B4:L" & lastRow).Copy
wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

End If

'delete the source worksheet since it has been appended to the main "Raw Data"
Application.DisplayAlerts = False
wsSource.Delete
Application.DisplayAlerts = True