[computerman29642]

I have created a macro in Excel 2003. Once I tested the macro several times, I placed the macro in a blank workbook, and saved it as "Personal.xls". I saved the "Personal.xls" workbook in this file path...

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

I opened another blank Excel workbook, and hide the Personal.xls window.

When I try running the macro from a button on the toolbar, I get the following Run-time error....

Run-time error '9'
Subscript out of range

This is the line of code that gets highlighted when I debug the error..

Set ws = ThisWorkbook.Sheets (.Name)

Can anyone help me to resolve this error?

[Aj_old]

The Personal.xls does not allow such thing as Thisworkbook, your better save the file as an Excel Add-in and set a reference to it so it will be loaded each time you open excel, and it allows you to work with thisworkbook

[computerman29642]

I saved the blank workbook as an"Add-in". I have checked it in the References.

I tried to setup a button on the toolbar to run the macro, but the macro did not show up as a choice. How do I run it now?

[Aj_old]

look here http://www.ozgrid.com/VBA/excel-add-in-create.htm

[computerman29642]

So, If I am reading this line correctly, "ThisWorkbook will always refer to the Add-in, not the users Workbook. Use ActiveWorkbook instead." I am still unable to use 'This workbook'...correct?

[Aj_old]

What ".Name" refers to ?

[computerman29642]

I believe it is used to insert the actual name of the worksheet.

Once I changed the 'This Work' to "Active Workbook" the code started to work.

[Aj_old]

If you don't have a line like "with ..." before it then it is the problem!

[computerman29642]

Here is the entire section of code..

With ActiveSheet
        Set ws = ActiveWorkbook.Sheets(.Name)
    End With

Thanks AJ!

[Aj_old]

Originally Posted by gamecockfan

Here is the entire section of code..

With ActiveSheet
        Set ws = ActiveWorkbook.Sheets(.Name)
    End With

Thanks AJ!

why not use:

Set ws = ActiveSheet

It should work fine.

[computerman29642]

Thanks AJ!

[Aj_old]

You are welcome