macro referencing to a hidden worksheet

[rdeters]

Firstly, I would like to say that your website is excellent. I have had some pretty detailed questions, but you were able to figure them out, cuddo's.
I have the following macro:

Sub Copying()
'
' Copying Macro
' Macro recorded 5/12/2002 by DETER
'

'
ActiveWindow.SmallScroll ToRight:=-1
Sheets("Working Database").Select
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.ScrollColumn = 1
Range("A1:G5250").Select
Selection.Copy
Sheets("IO Database").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

This macro works fine. What I want to do is hide the actual worksheet named "Working Database", with protection. But when I do this, and run the macro, it indicates a run time error becuase it can not find the worksheet. What do I need to change in the macro above for it to work on the hidden sheet?
Any help would be appreciative.

[Tom Schreiner]

Hi
I did not test this...
If the sheet is protected, you may need to unprotect it first...

Sub Copying()

'

' Copying Macro

' Macro recorded 5/12/2002 by DETER

'



'

ActiveWindow.SmallScroll ToRight:=-1

Sheets("Working Database").Visible = xlVisible

Sheets("Working Database").Select

ActiveWindow.SmallScroll ToRight:=2

ActiveWindow.ScrollColumn = 1

Range("A1:G5250").Select

Selection.Copy

Sheets("IO Database").Select

Range("A3").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Sheets("Working Database").Visible = xlHidden

End Sub

Tom

[rdeters]

I tried that macro and no luck. I get an error message that states:
"Run-time error '1004'", any other suggestions?

[Nimrod]

Hi rdeters:
The problem is that your attempting to select a hidden sheet. You can still copy and paste from hidden sheets you just can't select them. Starting with a macro is an excellent idea but what you need to do after that is go in and refine your code.
For example I've take you piece of code for selecting the copy area and refined it so that you don't have to select the sheet. Another reason you will want to refine your code is so that you don't have to watch you sheets jumping all over the place... instead the end result seems to magically appear.
Macro Code

Public Sub SelectFirst()
Sheets("Working Database").Select
Range("A1:G5").Select
Selection.Copy
end sub

Macro Code after fine tuning

Public Sub CopyWhileHidden()
Sheets("Working Database").Range("A1:G5").Copy
End Sub

I think you should be able to see the pattern here. All i'm doing is replacing the word select with what's on the next line.
You may want to go into your excel-vb help and do a search for RangeSelection Property to read up on this.

[ This Message was edited by: Nimrod on 2002-05-13 10:08 ]

[Tom Schreiner]

I gave you the wrong constant...
Still learning the object model here...

Change:

Sheets("Working Database").Visible = xlVisible

To:

Sheets("Working Database").Visible = xlSheetVisible

and

Change:

Sheets("Working Database").Visible = xlHidden

To:

Sheets("Working Database").Visible = xlSheetHidden

Tom

[rdeters]

Thanks for the help guys, both versions help me out enourmously, thanks.

[Nimrod]

Following the concept that I discussed in my last posting here is your macro ..."fine tuned".. see the method ?

Sub Copying()
' Copying Macro
' Macro recorded 5/12/2002 by DETER
Sheets("Working Database").Range("A1:G5250").Copy
Sheets("IO Datbase").Range("A1:G5250").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

[Tom Schreiner]

Nimrod
I didn't mean to post on top of you. I was too busy trying to figure out why I kept getting an error...
Thanks for the tip. I did not know you could work with a hidden sheet.
Maybe you wouldn't mind explaining the difference between Hidden and VeryHidden?
Thanks,
Tom

[Nimrod]

I'm sorry but I don't understand what you mean by " "Post on top". Is there a posting Etiquette that I'm not aware of ? If so please let me know as I don't want to offend anyone.

[Nimrod]

I don't use the very hidden but what I understand is the when a sheet is very hidden it cannot be unhidden through the menu. That to unhide a very hidden you must do it thru a macro. Is that your understanding ?