why does worksheet need to be active to manipulate?

[ilya2004]

Hi all,

I had some code that would manipulate cell values on different sheets. I am not sure what happened but it recently stopped running the code to change a cell's value if that worksheet isn't active.
Instead I get an application-defined or object-defined error.

I would even write out the full address of the code, for example

Sub Demo()
Sheets("Input").Range("A1")= "Test"
Sheets("Formulas").Range("b12")= "Test2"
End Sub

Unless the sheet is active, I get an error. It used to work fine, so I am not sure what happened. Any ideas?

 

[VoG]

That works for me. What is the exact error message and error code?

 

[shg]

The worksheets need not be active, but the workbook that contains those worksheets must be the active workbook.

Or you can specify the workbook.

 

[ilya2004]

I am getting Runtime error 1004
Select Method of Range Class Failed

I tried to precede the code with "ThisWorkbook." but I am still getting the errror. I am not sure it starting demanding this, I am working on exactly the same tool as I did before and it used to work just fine?

 

[Jonmo1]

I'm going to guess that the code you posted is not the actual code having problems.
Because that error is caused when you try to SELECT a range on a sheet that is not active...
Like
Sheets("Input").Range("A1").Select
Sheets("Input").Range("A1")= "Test"

If that is the case, remove the lines that select the ranges, and just use the lines you posted.

 

[ilya2004]

Yep, I tried the select already, I get the same error. The only thing that makes it go away is if I precede the code with sheets("Input").select and then have it select and modify the ranges. Then it seems to work fine.

 

[Jonmo1]

don't select anything...

The point of putting the sheetname in this line
Sheets("Input").Range("A1")= "Test"
is so that the sheet Input does not need to be active/selected to work.


Post the whole code if you want some help removing the select statements..

 

[mikerickson]

As Shg alluded to, have you tried this syntax

Workbooks("Workbook1.xlsm").Sheets("Input").Range("A1").Value = "Test"

Also are you sure there isn't a trailing space after the sheet tab name Input ?

 

[ilya2004]

The reason I didn't want to refer it to it by name is that the filename could be differnet and differnet times. Would this work with "ThisWorkbook"?

 

[shg]

ThisWorkbook refers to the workbook containing the code -- so if that is the case, yes.