Looping calculation

[wilg]

Hi there, need help with a looping calculation. The code below loops where I have put a comment "looping" beside it.
I have other code that sort the worksheet but after I sort the worksheets I still need the worksheets which range is between 1-7 to still move to before my "1 MAINT" sheet. Any suggestions on preventing this loop?

[VBA]Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Select Case Sh.Name
Case "1 maint", "2 status", "3 summary" '<--Change to the sheetnames you do NOT want the code to run against.//
Case Else
With Sh
If .Range("as5") > 7 Then
.Tab.ColorIndex = 5 ' BLUE




Else
If .Range("as1") = "n" Then
.Tab.ColorIndex = 3 ' RED
.Move After:=Sheets(Sheets.Count)
ThisWorkbook.Sheets("3 summary").Select



Else

If .Range("as5") > 1 And .Range("AS5") < 7 And .Range("as2") <> "Informed" Then

MsgBox .Range("AS1").Value & vbNewLine & vbNewLine & " IS COMING UP TO THEIR ANNIVERSARY DATE." & vbNewLine & vbNewLine & "PLEASE PRINT OUT ATTENDANCE AND CLEAR CELLS TO START A NEW YEAR.", vbInformation,
.Tab.ColorIndex = 6 ' yellow
.Move before:=Sheets("1 maint")
.Range("as2").FormulaR1C1 = "Informed"

else 'LOOPING STATEMENT
if .Range(as5") >1 And .Range("as5") <7 then
.Move before;Shetts("1 maint")
end if

End If
End If
End If




End With

End Select

End Sub[/VBA]


Sorry for the untidy code. Still learning that one.

[wilg]

Just a thought for this. Is there some code I could add to above in my calculation that woud work like.....

if sheet is after "1 maint" then
.move before sheet "1 maint"?
end if

This will prevent my issue with it continously looping? Im not sure how to make my suggestion work though...

[wilg]

One thing I need to note is that I am using worksheet on manual calculation, and it loops when I close the workbook as I have automatic calculation come back on at close.

[mdmackillop]

Posting a sample workbook would help us see the issue.
You could try running the code from the last sheet to the first

[VBA] Dim sh As Worksheet
Dim i As Long

For i = Sheets.Count To 1 Step -1
Set sh = Sheets(i)

Select Case sh.Name
'etc.
[/VBA]

BTW, You should always use Option Explicit to enforce variable declarations

[wilg]

The sheets I need to move to the front are alphabetical order so they may been anywhere in the middle of 250 sheets...
just a way that is once workbook calculates that it knows its a yellow sheet and move it to the front, then stop. My attempt loops moving the sheet to the front when auto calculation is on with the first code i posted above.

[wilg]

Ok, so how about this.

Is there a sub that can go through all my sheets in the workbook, pick out the yellow sheets and move them to the front on my workbook?
This will eliminate me putting this in worksheet calculation. I can call this sub after another sub if run to get the worksheets back in front.

Any thoughts?

[mdmackillop]

I like simple solutions!
[VBA]Sub MoveYellow()
Dim i As Long, j As Long

'Count yellow tabs
For i = 1 To Sheets.Count
If Sheets(i).Tab.ColorIndex = 6 Then j = j + 1
Next

'Move to front
For i = Sheets.Count To j + 1 Step -1
If Sheets(i).Tab.ColorIndex = 6 Then
Sheets(i).Move before:=Sheets(1)
i = i + 1
End If
Next
End Sub

[/VBA]