Auto_Deactivate can't find Macro

I have a simple range copy macro that I would like to have run when the "Data" sheet is deactivated. I defined the name as "Data!RitTest" and used the reference of "Deactivate_Test!RitTest.

The problem I can't fix is that the macro isn't found - I get the error : "Cannot find Deactivate_Test!RitTest.

I assume that I'm doing something fundamentally wrong in either the naming conventions or how I am trying to call the macro. Please let me know if anyone can help me with this problem.

Thanks.

CDEG

Hi

Deactivate is a sheet specific macro name.

So you need to save your code in the sheet specific area.

Right click pn the sheet name at the bottom of excel

On left tab select worksheet

on right tab select deactivate

paste you code between Sub and end sub.

I have an auto refreshing pivot table on the “report” sheet. This pivot table uses the data from the data sheet. Before I move from the data sheet, I want the formulas in CopyRange to copy down through Pivot_Range so that I know that all of the pivot table data is properly refreshed .
When I put the copy code into the worksheet module using Worksheet_Deactivate, and I move to the Report sheet, the copy macro runs fine, but I get a run-time error ‘1004’: unable to get the pivot tables property of the worksheet class.

Okay. That's great. Everything is working now and I only have one more problem. I know I've see nsolutions to similar problems, but I'm not sure they pertain to this specfic situation. When I move to the report sheet, the macro runs and end up back on the Data sheet. Is there something I can do to remain on the Report sheet?

Thanks, Norie. Yes, that does start an infinite loop.

Norie, I can't seem to get this to work - where exactly would this code be entered?

That is absolutely perfect! Thanks very much for your perserverance in getting this solution for me. It is very much appreciated.

Regards,

Craig