Strange error if another Excel book is opened
 

A bullet-proof application has been running for 5 years. I made a few small
changes and tested them. Everything worked perfect.

Now the weird part. The code works fine if the workbook is the <only
workbook open. If I open a second, unrelated workbook, EVEN A BLANK BOOK ONE
WITH NO MACROS, I get a "Subscript out of range" error. Why why why???

I should note that the code contains no Declare or Dim statements because
all my variables are used only within the module.

Any help is appreciated.

Paul

Here is the code:

Sub Findstore()

ActiveWindow.WindowState = xlMinimized

SHTNAME = ActiveSheet.Name

AAAA = Activecell.Value 'example of typical value: "555 New York"

'Below line is where the error occurs
Sheets("VAR1").Range("M8").Value = AAAA

If AAAA = "" Then AAAA = Range("C5").Value

Strange error if another Excel book is opened
 

the line Sheets("VAR1").Range("M8").Value = AAAA
may be trying to read from the other open workbook change code to this
thisworkbook.Sheets("VAR1").Range("M8").Value = AAAA
and see if that works
ben

"Paul" wrote:

A bullet-proof application has been running for 5 years. I made a few small
changes and tested them. Everything worked perfect.

Now the weird part. The code works fine if the workbook is the <only
workbook open. If I open a second, unrelated workbook, EVEN A BLANK BOOK ONE
WITH NO MACROS, I get a "Subscript out of range" error. Why why why???

I should note that the code contains no Declare or Dim statements because
all my variables are used only within the module.

Any help is appreciated.

Paul

Here is the code:

Sub Findstore()

ActiveWindow.WindowState = xlMinimized

SHTNAME = ActiveSheet.Name

AAAA = Activecell.Value 'example of typical value: "555 New York"

'Below line is where the error occurs
Sheets("VAR1").Range("M8").Value = AAAA

If AAAA = "" Then AAAA = Range("C5").Value