It could be that setting the values of the target cells causes
some lengthy recalculation, in which case it might help to turn
off automatic calculation. Turning off screen updating might
help a little too:

Application.ScreenUpdating = False
Dim saveCalcMode
' remember initial calc mode
saveCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
'
' your code here
'
' restore initial calc mode
Application.Calculation = saveCalcMode
Application.ScreenUpdating = True




On 11 Dec, 05:26, Carlee wrote:
I use the following nested if statement to pull values from one sheet, based
on a criteria. The if statement is super slow (2 minutes to run). I have
tried a case statement and it is about as slow.

What can i do to speed this up?

If Range("B4") = "January" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("C7:I10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("C14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("C17:C33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("C36:C46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("C49:C51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("C54:C60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("C63:C64").Value 'maintenance
ElseIf Range("B4") = "February" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("D7:D10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("D14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("D17:D33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("D36:D46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("D49:D51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("D54:D60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("D63:D64").Value 'maintenance
ElseIf Range("B4") = "March" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("E7:E10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("E14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("E17:E33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("E36:E46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("E49:E51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("E54:E60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("E63:E64").Value 'maintenance
ElseIf Range("B4") = "April" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("F7:F10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("F14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("F17:F33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("F36:F46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("F49:F51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("F54:F60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("F63:F64").Value 'maintenance
ElseIf Range("B4") = "May" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("G7:G10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("G14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("G17:G33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("G36:G46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("G49:G51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("G54:G60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("G63:G64").Value 'maintenance
ElseIf Range("B4") = "June" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("H7:H10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("I14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("H17:H33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("H36:H46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("H49:H51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("H54:60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("H63:H64").Value 'maintenance
ElseIf Range("B4") = "July" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("I7:I10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("I14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("I17:I33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("I36:I46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("I49:I51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("I54:I60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("I63:I64").Value 'maintenance
ElseIf Range("B4") = "August" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("J7:J10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("J14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("J17:J33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("J36:J46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("J49:J51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("J54:J60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("J63:J64").Value 'maintenance
ElseIf Range("B4") = "September" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("K7:K10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("K14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("K17:K33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("K36:K46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("K49:K51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("K54:K60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("K63:K64").Value 'maintenance
ElseIf Range("B4") = "October" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("L7:L10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("L14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("L17:L33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("L36:L46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("L49:L51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("L54:L60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("L63:L64").Value 'maintenance
ElseIf Range("B4") = "November" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("M7:M10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("M14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("M17:M33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("M36:M46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("M49:M51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("M54:M60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("M63:M64").Value 'maintenance
ElseIf Range("B4") = "December" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("N7:N10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("N14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("N17:N33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("N36:N46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("N49:N51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("N54:N60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("N63:N64").Value 'maintenance
Else
Exit Function
End If
--
Carlee