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

Thread: Hidden sheet problem(VBA)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have this code

    Sub SearchAllSheets()
    For Each Worksheet In Worksheets
    Sheets(Worksheet.Name).Select
    Call Main
    Next Worksheet
    End Sub

    which runs the macro for each sheet. I have one hidden sheet in the workbook which I don't want it to be run by the macro.
    However, when the code reach the hidden sheet I get an error in the line

    Sheets(Worksheet.Name).Select

    How can I solve this problem???Please help
    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi AllenL
    Change .Select to .Activate
    George
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Allen

    Do you need to select each sheet before running "Main" ? If really yes, then try:

    Code:
    Sub SearchAllSheets()
    Dim Wrksheet As Worksheet
        For Each Wrksheet In Worksheets
            If Wrksheet.Visible = xlSheetVisible Then
               Sheets(Worksheet.Name).Select
            End If
        Call Main
        Next Wrksheet
    End Sub

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry AllenL
    I misunderstood your post.
    .Select will hang on a hidden sheet but .Activate will not.
    Have a great day
    George
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi George
    If I change to Activate. The macro runs the hidden sheet(which I don't want that to happen).

    Any more idea?
    Thanks in advance

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Dave

    I am not really sure what's the difference between select the sheet then run "Main" and not select the sheet then run "Main"
    All I want to do is to have all the sheet to run "Main" except the hidden sheet
    I try your code but it gives me an error on the same line I had before

    Sheets(Worksheet.Name).Select

    Please Help
    Thanks in advance

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something like this to exclude the sheet.

    If Not (sht.Name = "MySheet") Then

    Sorry can't write the full code. the boss just came in.

    George
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this

    Sub SearchAllSheets()
    For Each worksheet In Worksheets
    If worksheet.Visible <> 0 Then
    Sheets(worksheet.Name).Select
    'Your code here
    End If
    Next worksheet
    End Sub

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
  •