Hi, I have a marco that selects the first column of a pivot table and changes the selected value to a different one. I have an error handler that half works. If the next requested data range in the table is not available the code will return the value back to the report and hi light it red. typically there is more than 1 error in a row. This parts works great. The part I cant figure out is once this happens I need to open the next selected value then close the one that was left open. So how do I hold that value in memory so I can close it.

In this example 730095 was the last selected. I can not close this without first opening another "MachineNumber" as there must always be something open in the pivot table. So if 315002 and 316503 have no data the pivot table cant open. but then 315004 has data and opens. but the last value "oldMachineNumber" is 730095 not 315002 as written in the code. When this happens 730095 will never close. 

Please Login or Register  to view this content.
Any help will be greatly appreciated.