macro referencing to a hidden worksheet

rdeters

New Member
Joined
May 6, 2002
Messages
40
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi
I did not test this...
If the sheet is protected, you may need to unprotect it first...

<pre>

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

</pre>

Tom
 
Upvote 0
I tried that macro and no luck. I get an error message that states:
"Run-time error '1004'", any other suggestions?
 
Upvote 0
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
Code:
Public Sub SelectFirst()
Sheets("Working Database").Select
Range("A1:G5").Select
Selection.Copy
end sub
Macro Code after fine tuning
Code:
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
 
Upvote 0
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
 
Upvote 0
Following the concept that I discussed in my last posting here is your macro ..."fine tuned".. see the method ?
Code:
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
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top