slow macro

[John_A]

I have a macro that updates a worksheet to ensure that the formulas stay
current. The worksheet pulls data off 12 other worksheets so that another
worksheet can display only pertinent data from the 12 combined. The purpose
of the code is to "fix" the worksheet if someone inserts, deletes, or pastes
cells on one of the 12. That being said, this is my code:

Sub Reset_Sheet()

Dim Current_Row As Integer
Dim iRow As Integer
Dim Months_Done As Boolean
Dim iMonth As Integer
Dim cMonth As String

Current_Row = 0
iRow = 0
Months_Done = False
iMonth = 1
cMonth = "Jan!"
Cells.Select
Selection.ClearContents
Sheets("Ref").Select
Application.ScreenUpdating = False

Do Until Months_Done = True

For iRow = 5 To 74
Current_Row = Current_Row + 1
Range("H" & Current_Row).Select
ActiveCell.Formula = "=if(" & cMonth & "H" & iRow & ">TODAY(),"
& cMonth & "H" & iRow & ")"
Range("A" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0," & cMonth &
"A" & iRow & ")"
Range("B" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"B" & iRow & ")"
Range("C" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"C" & iRow & ")"
Range("D" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"D" & iRow & ")"
Range("E" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"E" & iRow & ")"
Range("F" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"F" & iRow & ")"
Range("G" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"G" & iRow & ")"
Range("I" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"I" & iRow & ")"
Range("J" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"J" & iRow & ")"
Range("K" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"K" & iRow & ")"
Range("L" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"L" & iRow & ")"
Range("M" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"M" & iRow & ")"
Range("N" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<>0, " & cMonth &
"N" & iRow & ")"
Next iRow

iMonth = iMonth + 1
Select Case iMonth
Case 1: cMonth = "Jan!"
Case 2: cMonth = "Feb!"
Case 3: cMonth = "Mar!"
Case 4: cMonth = "Apr!"
Case 5: cMonth = "May!"
Case 6: cMonth = "Jun!"
Case 7: cMonth = "Jul!"
Case 8: cMonth = "Aug!"
Case 9: cMonth = "Sept!"
Case 10: cMonth = "Oct!"
Case 11: cMonth = "Nov!"
Case 12: cMonth = "Dec!"
Case 13: Months_Done = True
End Select
Loop

Application.ScreenUpdating = False

End Sub


Any help would be great!!

 

[ed]

You don't need to select the cell, but rather, can refer to it as an
object. This may speed up the response time.

Regards,
Eddie
http://www.HelpExcel.com

 

[Jim Thomlinson]

Try turning calculation off at the beginning and back on at the end...

Application.Calculation = xlCalculationManual
'Your code...
Application.Calculation = xlCalculationAutomatic

 

[John_A]

That worked really well! Thanks a lot!

Try turning calculation off at the beginning and back on at the end...

Application.Calculation = xlCalculationManual
'Your code...
Application.Calculation = xlCalculationAutomatic