Select methof of range class failed (1004)

[Schwizer]

I have piece of code that is giving me problems.

Please Login or Register  to view this content.

The sheet it refers to is hidden (not very hidden). The code sometimes works and sometimes it returns the generic 1004 error which is the most frustrating thing.

Anyone know what i could be doing wrong, some time of setting maybe?

Cheers
Bruno

[Simon Lloyd]

you cannot select a hidden sheet without first unhiding it!, i suggest you don't select it at all, how about using a with statement?
i.e
With Sheets("Sheet1").Cells
.Cells.Value = "Fred" ''''''change all cells to show "Fred"
End With

[Schwizer]

Alright thank you i'll try that. But why does it work sometimes?

Also, I tried to unhide the sheet to see if it would work then, but it still gives me the error on the second line regardless of whether the sheet is hidden or not. Also, the code works fine until i set application.updating to manual and back to automatic. After i set it to automatic, the code stops working.

[Simon Lloyd]

Schwizer, i cant answer that!, a glitch, a bug or maybe to do with using sheet codename i.e sheets(1) rather than sheet name Sheets("Sheet1")

[Schwizer]

Hmm, alright. Well basically all i want to do is find a cell on a hidden sheet using the search function.

How would i go about finding and selecting a cell called "456 Series" on a hidden sheet called "Motors"

Cheers
Schwizer

Note: Excel only has one bug, it's the user

[Simon Lloyd]

Well using the find method the sheet must be visible and active like below:

Please Login or Register  to view this content.

but with the above method the sheet is unhidden and hidden again without the user being aware!

You could declare Mc as a public string then once found it will be available in any module as that value

Please Login or Register  to view this content.

this way of declaring Mc as a public string can only be done in a standard module, you cannot declare a public string in a private module like a Userform