PDA

View Full Version : Solved: loop not looping properly



thole
05-26-2009, 12:08 PM
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

Bob Phillips
05-26-2009, 12:14 PM
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

thole
05-26-2009, 12:43 PM
Works. Thank you.