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
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