Macro stops working when I go to a different worksheet

**gandrea** · 08-30-2012, 04:46 PM

Hello,
The below code works as long as I am in "Peak WL 7914" worksheet. If I go to another worksheet I get the runtime error: "application defined or object defined error".
Please help, thanks in advance

Please Login or Register  to view this content.

**tbar05** · 08-30-2012, 07:03 PM

do you need to run the code for each sheet or does it only need to run on "Peak WL 7914"?

if only that sheet, just put:

Please Login or Register  to view this content.

at the start of your code

if you want it to run on each sheet then youll need to add something like

Please Login or Register  to view this content.

**jaslake** · 08-30-2012, 07:21 PM

@gandrea
@tbar05

Please add code tags around your code.

**gandrea** · 08-31-2012, 01:29 PM

tbar05,

Answering your question: I only need to run the code on the "Peak WL 7914" sheet.

I added .Select (see code below) as you suggested and the macro continues to run if i go to a different worksheet. However, when I am on a different sheet and the macro runs, I am taken back to the "Peak WL 7914" sheet. Is there a way to have the macro run and stay on the sheet that I am looking at?

Please Login or Register  to view this content.

Thanks

**BigBas** · 08-31-2012, 01:33 PM

Try changing this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Does that work? Please report back

**gandrea** · 08-31-2012, 01:38 PM

BigBas!! yes it works.

Please Login or Register  to view this content.

Thanks a million

**BigBas** · 08-31-2012, 01:40 PM

No problem.

It doesn't seem logical, but when you are referencing the Cells formula, it refers to the ActiveSheet if not qualified. Of course, this will throw an error in many cases. By adding the ".", it then references the "Peak WL 7914" sheet per the With statement.