Macro opens another workbook

Question

As I develop a worksheet, I rename it (xxx r1, xxx r2, xxx r3, etc.) before I move to the next area of updating. Now I am finished and when I run any of the macros it open the original worksheet. I want the macros to run only on the open worksheet, in this case the most recent version.

To complicate it a bit more, the worksheet I modified as above, has now been copied into another workbook with other worksheets that have nothing to do with the worksheet that has the macros.

I hope this makes sense to whomever is reading it.

Answer 1

Hi Tom,

Norman - Your response may be the answer, but I do not know how to 'replace hard-coded sheet names with the ActiveSheet property", I don't even know what 'hard-coded' or 'ActiveSheet property' refer to.

Please 'dumb down' your response with step by step process.

If the code is only to act on the active sheet, you could avoid the renaming problems by replacing instructions of the type:

       Worksheets("Sheet1").Range"A1").Value=100

with

       ActiveSheet.Range("A1").Value=100

However, if you post the problematic code, it should be possible to provide a more useful response.

===

Regards,

Norman

Answer 2

Hi Tom.

FIXED - Thank you......

Thank you for your kind feedback and I am glad that you have resolved the problem.

In order to close this thread, and to assist others who may search the forum archives for a solution to a similar problem, I would ask you kindly to mark my response as Answer.

===

Regards,

Norman

Answer 3

FIXED - Thank you......

Answer 4

Norman - Your response may be the answer, but I do not know how to 'replace hard-coded sheet names with the ActiveSheet property", I don't even know what 'hard-coded' or 'ActiveSheet property' refer to.

Please 'dumb down' your response with step by step process.

Thank you ... a very simple guy ... Tom

Answer 5

Hi Tom,

As I develop a worksheet, I rename it (xxx r1, xxx r2, xxx r3, etc.) before I move to the next area of updating. Now I am finished and when I run any of the macros it open the original worksheet. I want the macros to run only on the open worksheet, in this case the most recent version.

To complicate it a bit more, the worksheet I modified as above, has now been copied into another workbook with other worksheets that have nothing to do with the worksheet that has the macros.

I hope this makes sense to whomever is reading it.

If your code is intended only to act on the active sheet, replace hard-coded sheet names with the ActiveSheet property. Additionally, unless the code is event code, ensure that your code resides in a standard code module rather than the sheet's code module. If the code is worksheet event code, replace  sheet name references with the Me property. 

If you still have a problem, post the problematic code.

===

Regards,

Norman