|
slow macro
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!! |
slow macro
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 |
slow macro
Try turning calculation off at the beginning and back on at the end...
Application.Calculation = xlCalculationManual 'Your code... Application.Calculation = xlCalculationAutomatic -- HTH... Jim Thomlinson "John_A" wrote: 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!! |
slow macro
That worked really well! Thanks a lot!
"Jim Thomlinson" wrote: Try turning calculation off at the beginning and back on at the end... Application.Calculation = xlCalculationManual 'Your code... Application.Calculation = xlCalculationAutomatic -- HTH... Jim Thomlinson "John_A" wrote: 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!! |
| All times are GMT +1. The time now is 11:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com