Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: macro referencing to a hidden worksheet

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •