PDA

View Full Version : moving sheets



wilg
05-01-2011, 08:32 AM
Hi I have the below code which moves sheets to the front of my sheet "1 MAINT" if the sheet is yellow.

I need to incorperate if the sheet is already infront of "1 MAINT" sheet then don't do anything with that sheet.

The problem I have is the code in under sheetcalculate event and when I have multiple sheets that are yellow if I change something on another color sheet I keep getting kicked back to one of these yellow sheets because the code below is executing them to the front even though their already there.

Sub MoveYellow()
Dim i As Long, j As Long
For i = Sheets.Count To 4 Step -1
'MsgBox Sheets(i).Tab.ColorIndex
If Sheets(i).Tab.ColorIndex = 6 Then
Sheets(i).Move Before:=Sheets("1 maint")
End If
Next
End Sub

Bob Phillips
05-01-2011, 10:50 AM
Sub MoveYellow()
Dim i As Long, j As Long
Dim iMaint As Long

iMaint = Sheets("1 maint").Index
For i = Sheets.Count To 4 Step -1
'MsgBox Sheets(i).Tab.ColorIndex
If Sheets(i).Tab.ColorIndex = 6 Then
Sheets(i).Move Before:=Sheets("1 maint")
End If
If i >= iMaint Then Exit For
Next
End Sub

mikerickson
05-01-2011, 11:19 PM
Sub MoveYellow()
Dim i As Long, j As Long
For i = Sheets.Count To (Sheets("1 maint").Index + 1) Step -1
'MsgBox Sheets(i).Tab.ColorIndex
If Sheets(i).Tab.ColorIndex = 6 Then
Sheets(i).Move Before:=Sheets("1 maint")
End If
Next
End Sub