Hidden Sheets and Macro Operation .. or lack of it!

[Sam40mUK]

Hi All,
I have a spreadsheet with a macro saving to lists on seperate sheets and then retrieving from those lists at some later date.
To 'protect' these lists I have tried to Hide the relevant sheets but I end up with an error message when I run the macro ..

Run-time error '1004':
Select Method of Worksheet class failed

not really helpful!
May I therefore ask if anyone can phrase problem and suggest a possible solution, or a clue as to where to find one.
Thanks
S

[daleyman]

Firstly, hiding is not very good protection, is better to use protection. Or use both.

In the VBA you can unhide, do the operation, then hide again. You can likewise protect and unprotect.

But is better to have the code refer to the hidden sheets without using operations that require the sheets to be visible, such as .Select

Can you give example code???

[Sam40mUK]

To be honest I was just hoping to keep it simple, for now, and the ability to Hide Sheets seemed to be the easiest way - Out of sight, etc...
The code stalls on the first line of:

Sheets("DayWork Sheet").Select
Range("A16:I32").Select
Selection.Copy
Sheets("Hours Table").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Range("A1").Select
Sheets("DayWork Sheet").Select

Not pretty, I know! However this is merely to copy values from one sheet to another, the latter of which I would ideally like to hide.
I trust this helps clarify.
S

[daleyman]

This is your code again, but operating without .Select

Try it out...

Sheets("DayWork Sheet").Range("A16:I32").Copy
Sheets("Hours Table").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

[Mark O'Brien]

Sheets("DayWork Sheet").Select 

In the original question it seemed like you wanted to know what the "1004" error meant.

Basically what this is telling you is that Excel (or VBA) can't find a worksheet called "DayWork Sheet". You should check to make sure that you've got the name of the worksheet correct. What I used to do was to go to the worksheet tab, double click to highlight the name and then copy and paste that name directly into the VBE. This eliminated this error for me really easily until I really learned what it was about.

Incidentally, the more you learn VBA, the more times you'll find this error being raised because it's a fairly generic object error.

HTH