scottsacha
New Member
- Joined
- Mar 17, 2002
- Messages
- 3
I have created the folling macro below. It runs find untill it gets about half way through all the sheets. Then it starts not responding for a couple minutes and then it goes back to running. I do not understand what is causing it to go to a not responding status.
Sub VersionChange()
Dim Sheet As Worksheet
Dim act1 As String
Dim act2 As String
Dim For1 As String
Dim For2 As String
Dim ws As Worksheet
act1 = Worksheets("Ref+").Range("Actual1")
act2 = Worksheets("Ref+").Range("Actual2")
For1 = Worksheets("Ref+").Range("Forecast1")
For2 = Worksheets("Ref+").Range("Forecast2")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Sheet In ActiveWorkbook.Sheets
Set ws = Sheets(Sheet.Name)
If Right(Sheet.Name, 1) = "+" Then
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
Else
ws.Range(act1 & ":" & act2).Replace What:="($D", Replacement:="($A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(act1 & ":" & act2).Replace What:=",$E", Replacement:=",$B", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:="($A", Replacement:="($D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:=",$B", Replacement:=",$E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
End If
Next
Application.StatusBar = False
Application.ScreenUpdating = True
Sheets("Titles").Select
StatusMsgBox
Application.Calculation = xlCalculationAutomatic
Calculate
MsgBox "Update Complete"
End Sub
Sub StatusMsgBox()
CreateObject("WScript.Shell").Popup _
"Excel is starting to Calculate the Cells", 10, "ATTENTION"
End Sub
Sub VersionChange()
Dim Sheet As Worksheet
Dim act1 As String
Dim act2 As String
Dim For1 As String
Dim For2 As String
Dim ws As Worksheet
act1 = Worksheets("Ref+").Range("Actual1")
act2 = Worksheets("Ref+").Range("Actual2")
For1 = Worksheets("Ref+").Range("Forecast1")
For2 = Worksheets("Ref+").Range("Forecast2")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Sheet In ActiveWorkbook.Sheets
Set ws = Sheets(Sheet.Name)
If Right(Sheet.Name, 1) = "+" Then
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
Else
ws.Range(act1 & ":" & act2).Replace What:="($D", Replacement:="($A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(act1 & ":" & act2).Replace What:=",$E", Replacement:=",$B", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:="($A", Replacement:="($D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:=",$B", Replacement:=",$E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
End If
Next
Application.StatusBar = False
Application.ScreenUpdating = True
Sheets("Titles").Select
StatusMsgBox
Application.Calculation = xlCalculationAutomatic
Calculate
MsgBox "Update Complete"
End Sub
Sub StatusMsgBox()
CreateObject("WScript.Shell").Popup _
"Excel is starting to Calculate the Cells", 10, "ATTENTION"
End Sub