Issue with refreshing pivot tables in my code
 

Hi,

The code below is not finishing when executed -- it just hangs up -- hitting
he "ESC" key doesn't even stop it -- Excle stops responding and I have to
shut it down completely when I execute this query. I did a lot of testing on
it by using "Stop" at various points in the code to see if I could determine
exactly what line of code is causing the issue. What I found is that it
doesn't appear to be any one line of code causing the issue...what I found is
that the code takes longer and longer to finish executing the further down I
place "Stop" until it finally completely stops responding where I noted below
('This is where the code hangs up).

Any idea on why this is happening? I am testing this code with very little
data (4-7 lines of data which is what each pivot table is using). I am now
experimenting with foregoing the pivot table in the code all together and
essentially duplicating what the pivot table does by using For..Next and
If..Then loops -- is it recommended that one not use pivot tables with macros
as they tend to cause the code to crash? Just wondering if that's the
case...thanks!

Sub identifyopentrans()

Dim salesrange
Dim payrange
Dim A 'Variable for Order ID within salesrange
Dim B 'Variable for Order ID within payrange
Dim c 'Variable to determine if Order ID in Sales report is also within
Payment report
Dim x 'Variable to determine what cell within "Open Transaction" sheet to
paste Order ID into
Dim Aamount
Dim Bamount

Sheets("Open Transactions").Visible = True

Sheets("Open Transactions").Activate
Range(Rows(2), Rows(2).End(xlDown)).ClearContents

Sheets("Payment Sales Master Pivot").Activate
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

Sheets("Member ID Report Master Pivot").Activate
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh

Set salesrange = Worksheets("Member ID Report Master
Pivot").Range("A3").PivotTable.PivotFields("Order ID").DataRange
Set payrange = Worksheets("Payment Sales Master
Pivot").Range("A3").PivotTable.PivotFields("Order ID").DataRange

x = 1
For Each A In salesrange
c = 0
For Each B In payrange
If A = B Then
c = c + 1
End If
Next
If c < 1 Then
Sheets("Open Transactions").Range("D1").Offset(x, 0).value = A
Sheets("Open Transactions").Range("D1").Offset(x, -3).value =
Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0,
1).value
Sheets("Open Transactions").Range("D1").Offset(x, -2).value =
Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0,
2).value
Sheets("Open Transactions").Range("D1").Offset(x, -1).value =
Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0,
3).value
Sheets("Open Transactions").Range("D1").Offset(x, 1).value =
Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0,
4).value
Sheets("Open Transactions").Range("D1").Offset(x, 3).value =
"Payment not yet submitted for this Sales transaction"

x = x + 1

Else

'This is where the code hangs up

End If
Next

--
Robert