keep getting "debug error" [solved]

[ferday]

i have the following as a private code for a sheet. what it does is copy and paste tables from a hidden sheet, but only if the user chooses "yes". it works great, but even when i'm working on completely unrelated sheets, occasionally VBA will pop up the debug error. clicking "end" makes everything work fine again, until it pops up again. i'm hoping someone can help me figure out why i'm getting a debug error...i was thinking at first it might be related to worksheet_change but using _calculate or _selection stops the macro from working entirely.

thanks. the "debug" error always shows up on the first If InStr...line. if i delete that line, it shows up on the second instance, and so on

Please Login or Register  to view this content.

[walruseggman]

It's probably because of your use of ActiveSheet. I'm guessing Not all of the sheets in your workbook have Range("VALUE1") etc., thus the error.

Do you really want this code to be checking values on whatever sheet is active? If not, replace all your ActiveSheet's with the name of the Worksheet you want the code to be looking at. As example:

Please Login or Register  to view this content.

[ferday]

thank you so much! it was my bad, i assumed that the private code would only run when that sheet was active, so it seemed easiest to use activesheet...but i realized to use activesheet i should select.sheet first.

your fix is working perfectly

[walruseggman]

Great! Please mark this thread as SOLVED, and any reputation (the * in the lower left of my posts) is appreciated.