How to force macros refer to ActiveWorkbook

[zigzagdna]

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,

 

[zigzagdna]

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 havecreated 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 excelfile 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 tofinding 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.

 

[Ben McClave]

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