Consulting

Results 1 to 3 of 3

Thread: Loop not looping properly

  1. #1
    VBAX Regular
    Joined
    Mar 2008
    Posts
    24
    Location

    Loop not looping properly

    This loop is supposed to go through each worksheet that has a red tab, and isn't named after one of the exceptions and do a copy/paste values only.
    The only issue is that while it does loop, it does not loop properly: if I have 4 sheets that meet the criteria, it will select the first sheet and loop 4 times; if I have 2 sheets that meet the criteria, it will select the first sheet, and loop 2 times; and so on.
    How can I have it loop through ALL sheets once?

    Sub CopyPasteColumnB()
    Dim sh As Worksheet
    'Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Tab.ColorIndex = 3 And sh.Name <> "Rooms_List" And sh.Name <> "Cover_Sheet" And sh.Name <> "Product_Summary" Then
    Range("B65:B108").Select
        Selection.Copy
        Range("B65").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.SendKeys ("{ESC}")
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I am not sure what you mean by looping n times, but this works fine for me

    Sub CopyPasteColumnB()
    Dim sh As Worksheet
    'Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Tab.ColorIndex = 3 And _
                sh.Name <> "Rooms_List" And sh.Name <> "Cover_Sheet" And _
                sh.Name <> "Product_Summary" Then
    With sh.Range("B65:B108")
    .Value = .Value
                End With
            End If
        Next
    Application.ScreenUpdating = True
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Mar 2008
    Posts
    24
    Location
    Works. Thank you.

Posting Permissions

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