How to avoid using ThisWorkbook?

[LenaK]

Hello,

I'm using a routine that opens .xls files copies them and closes them back up. The problem is that I'm referring to the workbook I ran the procedure from as "ThisWorkbook" and if I have other Excel files open the routine fails. Is there a way to avoid using ThisWorkbook?

Thanks for your suggestion in advance

[TMS]

Not sure I understand your problem. ThisWorkbook always refers to the workbook that contains the code that is currently running. As opposed to the ActiveWorkbook, which is the currently active/selected workbook.

It is good practice to define and set variables to refer to the source and target workbooks when you want to copy between them. That ensures you don't experience the confusion that it seems you are doing. You also need to fully qualify any references to cells in the source and target workbooks.

Difficult to be more specific without seeing the code ... ideally with sample workbooks.

Regards, TMS

[LenaK]

Sorry I am new here.

I would like avoid hard-coding the workbook name, so if someone accidentally names the workbook differently the code will not work. The workbook is attached.

Thank you so much for your advice.

[TMS]

I don't think that it is "ThisWorkbook" that is giving you the problem. It's more likely:

Please Login or Register  to view this content.

Using a workbook's index is quite dangerous as opening and closing workbooks mean it can relate to a workbook other than the workbook you think it does.

You should use something like this to refer to ThisWorkbook:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

In that way, you can explicitly refer to, or Select (if you must, although you rarely need to), the source and target workbooks.



Regards, TMS

[LenaK]

So when I refer to the Workbook being copied from I should say:

Please Login or Register  to view this content.

Correct?

[LenaK]

Brilliant! it worked!!!

[TMS]

You're welcome. Thanks for the rep.