+ Reply to Thread
Results 1 to 2 of 2

Subroutine loop -- Stops after first, active sheet

Hybrid View

za20001 Subroutine loop -- Stops... 05-02-2011, 02:32 PM
Leith Ross Re: Subroutine loop -- Stops... 05-02-2011, 02:51 PM
  1. #1
    Registered User
    Join Date
    04-26-2011
    Location
    20001
    MS-Off Ver
    Excel 2010
    Posts
    4

    Subroutine loop -- Stops after first, active sheet

    Trying to loop a subroutine through x worksheets, where x < n and n is the total number of worksheets in ActiveWorkbook. Worksheets in X have unique names (character strings). My sub keeps running on the first sheet in the book and no other. Help?

    Sub countpiece()
    Dim ws As Worksheet
    Dim i As Long, j As Long, k As Long, g As Long, h As Long
    
    i = 0
    j = 0
    k = 0
    g = 0
    h = 0
    
    Application.EnableCancelKey = xlDisabled
    
    For Each Worksheet In ActiveWorkbook.Worksheets
        
        Select Case Worksheet.Name
            
            Case "abur", ... (more sheet names here, not including the first, active sheet's name)(code following sheet names contains instructions for operations. Condition/repeat structure ends like this:)
    
                Case Else
    
    End Select
    
    Next Worksheet
    
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Subroutine loop -- Stops after first, active sheet

    Hello za20001,

    You were using the worksheet object "Worksheet" instead of the object variable "ws". here is the corrected code.
    Sub countpiece()
    Dim ws As Worksheet
    Dim i As Long, j As Long, k As Long, g As Long, h As Long
    
    i = 0
    j = 0
    k = 0
    g = 0
    h = 0
    
    Application.EnableCancelKey = xlDisabled
    
    For Each ws In ActiveWorkbook.Worksheets
        
        Select Case ws.Name
            
            Case "abur"   ', ... (more sheet names here, not including the first, active sheet's name)(code following sheet names contains instructions for operations. Condition/repeat structure ends like this:)
    
                Case Else
    
    End Select
    
    Next Worksheet
    
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1