Macro doesn't work with another workbook acitive

[kioolt]

The macro below runs fine until another workbook is the active workbook. What can I do to change this macro to make it work while I have another workbook open and active. The macro works fine as long as the workbook to which it belongs is the active workbook. It will halt on the line beginning with Set rnAR_Dest with an Error 9 Subscript out of range.

Please Login or Register  to view this content.

[stnkynts]

You would need to declare the workbook infront of the worksheet. Without it you will always be referencing the active workbook. Example:

Please Login or Register  to view this content.

Note the use of the extension. You will need too apply it where neccessary

[kioolt]

That did the trick. I had to apply it to about half a dozen places. Is there a way to get the workbook name that this module is in? The name of the workbook is "MyWorkbook 2014". Next year I will start with the data removed and have a new workbook named "MyWorkbook 2015". It's not much trouble but I will have to change the macro every year unless I can insert code that will use the right name for me. Something WorkBookName = Workbooks.Name, which I tired but doesn't work.

[HaHoBe]

Hi, kioolt,

please add code-tags to your procedure in the opening post.

If the workbook holds the code as well you could rely on ThisWorkbook instead of Workbooks("MyWorkbook 2014.xlsm").

Ciao,
Holger

[kioolt]

Thanks,

Using ThisWorkbook is working just fine.