How to force macros refer to ActiveWorkbook
 

Subject: How to force macros refer to ActiveWorkbook
I am on Excel 2007. I am struggling to write macros which are stored in a central place.
I have created macros in a file SCmacros.xlsm, I open this file. Macro files refer to various worksheets. These worksheets do not exist in macros file; instead they exist in SCdata.xls file discussed below,
I now have another excel file SCdata.xls which calls macros in SCmacros.xlsm, I open in the SCdata.xls file and call a macro SCmacros.xlsm!test

Problem is test macro seems to finding worksheets in SCmacros.xlsm file instead of in ActiveWorkbook Scdata.xls. How can I make simple changes such as setting up some events in SCmacros.xlsm macros refer to Worksheets in ActiveWorkbook Scdata.xls. I did try to put :
ThisWorkbook = ActiveWorkbook
n my test macro but it did not work!!
I do not want to make SCmacros.xlsm a plug in because that creates another set of problems.

Thanks a lot,

How to force macros refer to ActiveWorkbook
 

On Friday, September 21, 2012 9:05:18 PM UTC-4, (unknown) wrote:
Subject: How to force macros refer to ActiveWorkbook I am on Excel 2007. I am struggling to write macros which are stored in a central place. I have created macros in a file SCmacros.xlsm, I open this file. Macro files refer to various worksheets. These worksheets do not exist in macros file; instead they exist in SCdata.xls file discussed below, I now have another excel file SCdata.xls which calls macros in SCmacros.xlsm, I open in the SCdata.xls file and call a macro SCmacros.xlsm!test Problem is test macro seems to finding worksheets in SCmacros.xlsm file instead of in ActiveWorkbook Scdata.xls. How can I make simple changes such as setting up some events in SCmacros.xlsm macros refer to Worksheets in ActiveWorkbook Scdata.xls. I did try to put : ThisWorkbook = ActiveWorkbook n my test macro but it did not work!! I do not want to make SCmacros.xlsm a plug in because that creates another set of problems. Thanks a lot,

I have even tried to all my marcos in personal.xls file, but when I rn macro from Scdata.xls file (View macros, select personal.xls!test macr and run), test macro errors that worksheel in Scdata.xls do not exist; then what is poinyt of putting macros in personal.xls.

How to force macros refer to ActiveWorkbook
 

Hello,

Assuming that your macros will be called from whatever workbook is active, you may consider adding this to each macro:

Dim wbThisWB As Workbook
Set wbThisWB = ActiveWorkbook

Then, when you reference sheets in your macro (for example "MySheet"), use the "wbThisWB" object. For example:

wbThisWB.Sheets("Sheet3").Activate

Best of luck,

Ben