Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Slooooooow
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Slooooooow
Try something like this:
Dim x as integer Dim ws as worksheet set ws=Worksheets("YCT") Select Case Range("B4").value Case "January": x=1 Case "February": x=2 Case "March": x=3 ... Case "December": x=12 Case Else: x=-1 End select 'handle -1 case by msgbox and exit.... If x < -1 Then Range("AI7:AI10").Value = ws.Range("C7:I10").Offset(0, x-1).Value ... Range("AI63:AI64").Value = ws.Range("C63:C64").Offset(0, x-1).Value End If Tim "Carlee" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Slooooooow
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |