| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a faster way
If you really care about the cells that you skipped...
Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Hi there,
I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Post your code...
-- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Here is the code i am using
Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Range("AI7:AI42").Value = Worksheets("Year at a Glance").Range("C5:C39").Value -- Jim "Carlee" wrote in message ... | Here is the code i am using | | Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) | Case Is = "JANUARY" | Range("AI7") = Worksheets("Year at a Glance").Range("C5") | Range("AI8") = Worksheets("Year at a Glance").Range("C6") | Range("AI9") = Worksheets("Year at a Glance").Range("C7") | Range("AI10") = Worksheets("Year at a Glance").Range("C8") | Range("AI11") = Worksheets("Year at a Glance").Range("C9") | Range("AI12") = Worksheets("Year at a Glance").Range("C10") | Range("AI13") = Worksheets("Year at a Glance").Range("C11") | Range("AI14") = Worksheets("Year at a Glance").Range("C12") | Range("AI16") = Worksheets("Year at a Glance").Range("C14") | Range("AI17") = Worksheets("Year at a Glance").Range("C15") | Range("AI18") = Worksheets("Year at a Glance").Range("C16") | Range("AI19") = Worksheets("Year at a Glance").Range("C17") | Range("AI21") = Worksheets("Year at a Glance").Range("C19") | Range("AI22") = Worksheets("Year at a Glance").Range("C20") | Range("AI23") = Worksheets("Year at a Glance").Range("C21") | Range("AI24") = Worksheets("Year at a Glance").Range("C22") | Range("AI25") = Worksheets("Year at a Glance").Range("C23") | Range("AI26") = Worksheets("Year at a Glance").Range("C24") | Range("AI28") = Worksheets("Year at a Glance").Range("C26") | Range("AI29") = Worksheets("Year at a Glance").Range("C27") | Range("AI30") = Worksheets("Year at a Glance").Range("C28") | Range("AI31") = Worksheets("Year at a Glance").Range("C29") | Range("AI33") = Worksheets("Year at a Glance").Range("C31") | Range("AI34") = Worksheets("Year at a Glance").Range("C32") | Range("AI35") = Worksheets("Year at a Glance").Range("C33") | Range("AI38") = Worksheets("Year at a Glance").Range("C35") | Range("AI39") = Worksheets("Year at a Glance").Range("C36") | Range("AI40") = Worksheets("Year at a Glance").Range("C37") | Range("AI41") = Worksheets("Year at a Glance").Range("C38") | Range("AI42") = Worksheets("Year at a Glance").Range("C39") | Case Is = "FEBRUARY" | Range("AI7") = Worksheets("Year at a Glance").Range("D5") | Range("AI8") = Worksheets("Year at a Glance").Range("D6") | Range("AI9") = Worksheets("Year at a Glance").Range("D7") | Range("AI10") = Worksheets("Year at a Glance").Range("D8") | Range("AI11") = Worksheets("Year at a Glance").Range("D9") | Range("AI12") = Worksheets("Year at a Glance").Range("D10") | Range("AI13") = Worksheets("Year at a Glance").Range("D11") | Range("AI14") = Worksheets("Year at a Glance").Range("D12") | Range("AI16") = Worksheets("Year at a Glance").Range("D14") | Range("AI17") = Worksheets("Year at a Glance").Range("D15") | Range("AI18") = Worksheets("Year at a Glance").Range("D16") | Range("AI19") = Worksheets("Year at a Glance").Range("D17") | Range("AI21") = Worksheets("Year at a Glance").Range("D19") | Range("AI22") = Worksheets("Year at a Glance").Range("D20") | Range("AI23") = Worksheets("Year at a Glance").Range("D21") | Range("AI24") = Worksheets("Year at a Glance").Range("D22") | Range("AI25") = Worksheets("Year at a Glance").Range("D23") | Range("AI26") = Worksheets("Year at a Glance").Range("D24") | Range("AI28") = Worksheets("Year at a Glance").Range("D26") | Range("AI29") = Worksheets("Year at a Glance").Range("D27") | Range("AI30") = Worksheets("Year at a Glance").Range("D28") | Range("AI31") = Worksheets("Year at a Glance").Range("D29") | Range("AI33") = Worksheets("Year at a Glance").Range("D31") | Range("AI34") = Worksheets("Year at a Glance").Range("D32") | Range("AI35") = Worksheets("Year at a Glance").Range("D33") | Range("AI38") = Worksheets("Year at a Glance").Range("D35") | Range("AI39") = Worksheets("Year at a Glance").Range("D36") | Range("AI40") = Worksheets("Year at a Glance").Range("D37") | Range("AI41") = Worksheets("Year at a Glance").Range("D38") | Range("AI42") = Worksheets("Year at a Glance").Range("D39") | Case Is = "MARCH" | Range("AI7") = Worksheets("Year at a Glance").Range("E5") | Range("AI8") = Worksheets("Year at a Glance").Range("E6") | Range("AI9") = Worksheets("Year at a Glance").Range("E7") | Range("AI10") = Worksheets("Year at a Glance").Range("E8") | Range("AI11") = Worksheets("Year at a Glance").Range("E9") | Range("AI12") = Worksheets("Year at a Glance").Range("E10") | Range("AI13") = Worksheets("Year at a Glance").Range("E11") | Range("AI14") = Worksheets("Year at a Glance").Range("E12") | Range("AI16") = Worksheets("Year at a Glance").Range("E14") | Range("AI17") = Worksheets("Year at a Glance").Range("E15") | Range("AI18") = Worksheets("Year at a Glance").Range("E16") | Range("AI19") = Worksheets("Year at a Glance").Range("E17") | Range("AI21") = Worksheets("Year at a Glance").Range("E19") | Range("AI22") = Worksheets("Year at a Glance").Range("E20") | Range("AI23") = Worksheets("Year at a Glance").Range("E21") | Range("AI24") = Worksheets("Year at a Glance").Range("E22") | Range("AI25") = Worksheets("Year at a Glance").Range("E23") | Range("AI26") = Worksheets("Year at a Glance").Range("E24") | Range("AI28") = Worksheets("Year at a Glance").Range("E26") | Range("AI29") = Worksheets("Year at a Glance").Range("E27") | Range("AI30") = Worksheets("Year at a Glance").Range("E28") | Range("AI31") = Worksheets("Year at a Glance").Range("E29") | Range("AI33") = Worksheets("Year at a Glance").Range("E31") | Range("AI34") = Worksheets("Year at a Glance").Range("E32") | Range("AI35") = Worksheets("Year at a Glance").Range("E33") | Range("AI38") = Worksheets("Year at a Glance").Range("E35") | Range("AI39") = Worksheets("Year at a Glance").Range("E36") | Range("AI40") = Worksheets("Year at a Glance").Range("E37") | Range("AI41") = Worksheets("Year at a Glance").Range("E38") | Range("AI42") = Worksheets("Year at a Glance").Range("E39") | Case Is = "APRIL" | Range("AI7") = Worksheets("Year at a Glance").Range("F5") | Range("AI8") = Worksheets("Year at a Glance").Range("F6") | Range("AI9") = Worksheets("Year at a Glance").Range("F7") | Range("AI10") = Worksheets("Year at a Glance").Range("F8") | Range("AI11") = Worksheets("Year at a Glance").Range("F9") | Range("AI12") = Worksheets("Year at a Glance").Range("F10") | Range("AI13") = Worksheets("Year at a Glance").Range("F11") | Range("AI14") = Worksheets("Year at a Glance").Range("F12") | Range("AI16") = Worksheets("Year at a Glance").Range("F14") | Range("AI17") = Worksheets("Year at a Glance").Range("F15") | Range("AI18") = Worksheets("Year at a Glance").Range("F16") | Range("AI19") = Worksheets("Year at a Glance").Range("F17") | Range("AI21") = Worksheets("Year at a Glance").Range("F19") | Range("AI22") = Worksheets("Year at a Glance").Range("F20") | Range("AI23") = Worksheets("Year at a Glance").Range("F21") | Range("AI24") = Worksheets("Year at a Glance").Range("F22") | Range("AI25") = Worksheets("Year at a Glance").Range("F23") | Range("AI26") = Worksheets("Year at a Glance").Range("F24") | Range("AI28") = Worksheets("Year at a Glance").Range("F26") | Range("AI29") = Worksheets("Year at a Glance").Range("F27") | Range("AI30") = Worksheets("Year at a Glance").Range("F28") | Range("AI31") = Worksheets("Year at a Glance").Range("F29") | Range("AI33") = Worksheets("Year at a Glance").Range("F31") | Range("AI34") = Worksheets("Year at a Glance").Range("F32") | Range("AI35") = Worksheets("Year at a Glance").Range("F33") | Range("AI38") = Worksheets("Year at a Glance").Range("F35") | Range("AI39") = Worksheets("Year at a Glance").Range("F36") | Range("AI40") = Worksheets("Year at a Glance").Range("F37") | Range("AI41") = Worksheets("Year at a Glance").Range("F38") | Range("AI42") = Worksheets("Year at a Glance").Range("F39") | | Case Is = "MAY" | Range("AI7") = Worksheets("Year at a Glance").Range("G5") | Range("AI8") = Worksheets("Year at a Glance").Range("G6") | Range("AI9") = Worksheets("Year at a Glance").Range("G7") | Range("AI10") = Worksheets("Year at a Glance").Range("G8") | Range("AI11") = Worksheets("Year at a Glance").Range("G9") | Range("AI12") = Worksheets("Year at a Glance").Range("G10") | Range("AI13") = Worksheets("Year at a Glance").Range("G11") | Range("AI14") = Worksheets("Year at a Glance").Range("G12") | Range("AI16") = Worksheets("Year at a Glance").Range("G14") | Range("AI17") = Worksheets("Year at a Glance").Range("G15") | Range("AI18") = Worksheets("Year at a Glance").Range("G16") | Range("AI19") = Worksheets("Year at a Glance").Range("G17") | Range("AI21") = Worksheets("Year at a Glance").Range("G19") | Range("AI22") = Worksheets("Year at a Glance").Range("G20") | Range("AI23") = Worksheets("Year at a Glance").Range("G21") | Range("AI24") = Worksheets("Year at a Glance").Range("G22") | Range("AI25") = Worksheets("Year at a Glance").Range("G23") | Range("AI26") = Worksheets("Year at a Glance").Range("G24") | Range("AI28") = Worksheets("Year at a Glance").Range("G26") | Range("AI29") = Worksheets("Year at a Glance").Range("G27") | Range("AI30") = Worksheets("Year at a Glance").Range("G28") | Range("AI31") = Worksheets("Year at a Glance").Range("G29") | Range("AI33") = Worksheets("Year at a Glance").Range("G31") | Range("AI34") = Worksheets("Year at a Glance").Range("G32") | Range("AI35") = Worksheets("Year at a Glance").Range("G33") | Range("AI38") = Worksheets("Year at a Glance").Range("G35") | Range("AI39") = Worksheets("Year at a Glance").Range("G36") | Range("AI40") = Worksheets("Year at a Glance").Range("G37") | Range("AI41") = Worksheets("Year at a Glance").Range("G38") | Range("AI42") = Worksheets("Year at a Glance").Range("G39") | | Case Is = "JUNE" | Range("AI7") = Worksheets("Year at a Glance").Range("H5") | Range("AI8") = Worksheets("Year at a Glance").Range("H6") | Range("AI9") = Worksheets("Year at a Glance").Range("H7") | Range("AI10") = Worksheets("Year at a Glance").Range("H8") | Range("AI11") = Worksheets("Year at a Glance").Range("H9") | Range("AI12") = Worksheets("Year at a Glance").Range("H10") | Range("AI13") = Worksheets("Year at a Glance").Range("H11") | Range("AI14") = Worksheets("Year at a Glance").Range("H12") | Range("AI16") = Worksheets("Year at a Glance").Range("H14") | Range("AI17") = Worksheets("Year at a Glance").Range("H15") | Range("AI18") = Worksheets("Year at a Glance").Range("H16") | Range("AI19") = Worksheets("Year at a Glance").Range("H17") | Range("AI21") = Worksheets("Year at a Glance").Range("H19") | Range("AI22") = Worksheets("Year at a Glance").Range("H20") | Range("AI23") = Worksheets("Year at a Glance").Range("H21") | Range("AI24") = Worksheets("Year at a Glance").Range("H22") | Range("AI25") = Worksheets("Year at a Glance").Range("H23") | Range("AI26") = Worksheets("Year at a Glance").Range("H24") | Range("AI28") = Worksheets("Year at a Glance").Range("H26") | Range("AI29") = Worksheets("Year at a Glance").Range("H27") | Range("AI30") = Worksheets("Year at a Glance").Range("H28") | Range("AI31") = Worksheets("Year at a Glance").Range("H29") | Range("AI33") = Worksheets("Year at a Glance").Range("H31") | Range("AI34") = Worksheets("Year at a Glance").Range("H32") | Range("AI35") = Worksheets("Year at a Glance").Range("H33") | Range("AI38") = Worksheets("Year at a Glance").Range("H35") | Range("AI39") = Worksheets("Year at a Glance").Range("H36") | Range("AI40") = Worksheets("Year at a Glance").Range("H37") | Range("AI41") = Worksheets("Year at a Glance").Range("H38") | Range("AI42") = Worksheets("Year at a Glance").Range("H39") | | Case Is = "JULY" | Range("AI7") = Worksheets("Year at a Glance").Range("I5") | Range("AI8") = Worksheets("Year at a Glance").Range("I6") | Range("AI9") = Worksheets("Year at a Glance").Range("I7") | Range("AI10") = Worksheets("Year at a Glance").Range("I8") | Range("AI11") = Worksheets("Year at a Glance").Range("I9") | Range("AI12") = Worksheets("Year at a Glance").Range("I10") | Range("AI13") = Worksheets("Year at a Glance").Range("I11") | Range("AI14") = Worksheets("Year at a Glance").Range("I12") | Range("AI16") = Worksheets("Year at a Glance").Range("I14") | Range("AI17") = Worksheets("Year at a Glance").Range("I15") | Range("AI18") = Worksheets("Year at a Glance").Range("I16") | Range("AI19") = Worksheets("Year at a Glance").Range("I17") | Range("AI21") = Worksheets("Year at a Glance").Range("I19") | Range("AI22") = Worksheets("Year at a Glance").Range("I20") | Range("AI23") = Worksheets("Year at a Glance").Range("I21") | Range("AI24") = Worksheets("Year at a Glance").Range("I22") | Range("AI25") = Worksheets("Year at a Glance").Range("I23") | Range("AI26") = Worksheets("Year at a Glance").Range("I24") | Range("AI28") = Worksheets("Year at a Glance").Range("I26") | Range("AI29") = Worksheets("Year at a Glance").Range("I27") | Range("AI30") = Worksheets("Year at a Glance").Range("I28") | Range("AI31") = Worksheets("Year at a Glance").Range("I29") | Range("AI33") = Worksheets("Year at a Glance").Range("I31") | Range("AI34") = Worksheets("Year at a Glance").Range("I32") | Range("AI35") = Worksheets("Year at a Glance").Range("I33") | Range("AI38") = Worksheets("Year at a Glance").Range("I35") | Range("AI39") = Worksheets("Year at a Glance").Range("I36") | Range("AI40") = Worksheets("Year at a Glance").Range("I37") | Range("AI41") = Worksheets("Year at a Glance").Range("I38") | Range("AI42") = Worksheets("Year at a Glance").Range("I39") | | Case Is = "AUGUST" | Range("AI7") = Worksheets("Year at a Glance").Range("J5") | Range("AI8") = Worksheets("Year at a Glance").Range("J6") | Range("AI9") = Worksheets("Year at a Glance").Range("J7") | Range("AI10") = Worksheets("Year at a Glance").Range("J8") | Range("AI11") = Worksheets("Year at a Glance").Range("J9") | Range("AI12") = Worksheets("Year at a Glance").Range("J10") | Range("AI13") = Worksheets("Year at a Glance").Range("J11") | Range("AI14") = Worksheets("Year at a Glance").Range("J12") | Range("AI16") = Worksheets("Year at a Glance").Range("J14") | Range("AI17") = Worksheets("Year at a Glance").Range("J15") | Range("AI18") = Worksheets("Year at a Glance").Range("J16") | Range("AI19") = Worksheets("Year at a Glance").Range("J17") | Range("AI21") = Worksheets("Year at a Glance").Range("J19") | Range("AI22") = Worksheets("Year at a Glance").Range("J20") | Range("AI23") = Worksheets("Year at a Glance").Range("J21") | Range("AI24") = Worksheets("Year at a Glance").Range("J22") | Range("AI25") = Worksheets("Year at a Glance").Range("J23") | Range("AI26") = Worksheets("Year at a Glance").Range("J24") | Range("AI28") = Worksheets("Year at a Glance").Range("J26") | Range("AI29") = Worksheets("Year at a Glance").Range("J27") | Range("AI30") = Worksheets("Year at a Glance").Range("J28") | Range("AI31") = Worksheets("Year at a Glance").Range("J29") | Range("AI33") = Worksheets("Year at a Glance").Range("J31") | Range("AI34") = Worksheets("Year at a Glance").Range("J32") | Range("AI35") = Worksheets("Year at a Glance").Range("J33") | Range("AI38") = Worksheets("Year at a Glance").Range("J35") | Range("AI39") = Worksheets("Year at a Glance").Range("J36") | Range("AI40") = Worksheets("Year at a Glance").Range("J37") | Range("AI41") = Worksheets("Year at a Glance").Range("J38") | Range("AI42") = Worksheets("Year at a Glance").Range("J39") | | Case Is = "SEPTEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("K5") | Range("AI8") = Worksheets("Year at a Glance").Range("K6") | Range("AI9") = Worksheets("Year at a Glance").Range("K7") | Range("AI10") = Worksheets("Year at a Glance").Range("K8") | Range("AI11") = Worksheets("Year at a Glance").Range("K9") | Range("AI12") = Worksheets("Year at a Glance").Range("K10") | Range("AI13") = Worksheets("Year at a Glance").Range("K11") | Range("AI14") = Worksheets("Year at a Glance").Range("K12") | Range("AI16") = Worksheets("Year at a Glance").Range("K14") | Range("AI17") = Worksheets("Year at a Glance").Range("K15") | Range("AI18") = Worksheets("Year at a Glance").Range("K16") | Range("AI19") = Worksheets("Year at a Glance").Range("K17") | Range("AI21") = Worksheets("Year at a Glance").Range("K19") | Range("AI22") = Worksheets("Year at a Glance").Range("K20") | Range("AI23") = Worksheets("Year at a Glance").Range("K21") | Range("AI24") = Worksheets("Year at a Glance").Range("K22") | Range("AI25") = Worksheets("Year at a Glance").Range("K23") | Range("AI26") = Worksheets("Year at a Glance").Range("K24") | Range("AI28") = Worksheets("Year at a Glance").Range("K26") | Range("AI29") = Worksheets("Year at a Glance").Range("K27") | Range("AI30") = Worksheets("Year at a Glance").Range("K28") | Range("AI31") = Worksheets("Year at a Glance").Range("K29") | Range("AI33") = Worksheets("Year at a Glance").Range("K31") | Range("AI34") = Worksheets("Year at a Glance").Range("K32") | Range("AI35") = Worksheets("Year at a Glance").Range("K33") | Range("AI38") = Worksheets("Year at a Glance").Range("K35") | Range("AI39") = Worksheets("Year at a Glance").Range("K36") | Range("AI40") = Worksheets("Year at a Glance").Range("K37") | Range("AI41") = Worksheets("Year at a Glance").Range("K38") | Range("AI42") = Worksheets("Year at a Glance").Range("K39") | | Case Is = "OCTOBER" | Range("AI7") = Worksheets("Year at a Glance").Range("L5") | Range("AI8") = Worksheets("Year at a Glance").Range("L6") | Range("AI9") = Worksheets("Year at a Glance").Range("L7") | Range("AI10") = Worksheets("Year at a Glance").Range("L8") | Range("AI11") = Worksheets("Year at a Glance").Range("L9") | Range("AI12") = Worksheets("Year at a Glance").Range("L10") | Range("AI13") = Worksheets("Year at a Glance").Range("L11") | Range("AI14") = Worksheets("Year at a Glance").Range("L12") | Range("AI16") = Worksheets("Year at a Glance").Range("L14") | Range("AI17") = Worksheets("Year at a Glance").Range("L15") | Range("AI18") = Worksheets("Year at a Glance").Range("L16") | Range("AI19") = Worksheets("Year at a Glance").Range("L17") | Range("AI21") = Worksheets("Year at a Glance").Range("L19") | Range("AI22") = Worksheets("Year at a Glance").Range("L20") | Range("AI23") = Worksheets("Year at a Glance").Range("L21") | Range("AI24") = Worksheets("Year at a Glance").Range("L22") | Range("AI25") = Worksheets("Year at a Glance").Range("L23") | Range("AI26") = Worksheets("Year at a Glance").Range("L24") | Range("AI28") = Worksheets("Year at a Glance").Range("L26") | Range("AI29") = Worksheets("Year at a Glance").Range("L27") | Range("AI30") = Worksheets("Year at a Glance").Range("L28") | Range("AI31") = Worksheets("Year at a Glance").Range("L29") | Range("AI33") = Worksheets("Year at a Glance").Range("L31") | Range("AI34") = Worksheets("Year at a Glance").Range("L32") | Range("AI35") = Worksheets("Year at a Glance").Range("L33") | Range("AI38") = Worksheets("Year at a Glance").Range("L35") | Range("AI39") = Worksheets("Year at a Glance").Range("L36") | Range("AI40") = Worksheets("Year at a Glance").Range("L37") | Range("AI41") = Worksheets("Year at a Glance").Range("L38") | Range("AI42") = Worksheets("Year at a Glance").Range("L39") | | | Case Is = "NOVEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("M5") | Range("AI8") = Worksheets("Year at a Glance").Range("M6") | Range("AI9") = Worksheets("Year at a Glance").Range("M7") | Range("AI10") = Worksheets("Year at a Glance").Range("M8") | Range("AI11") = Worksheets("Year at a Glance").Range("M9") | Range("AI12") = Worksheets("Year at a Glance").Range("M10") | Range("AI13") = Worksheets("Year at a Glance").Range("M11") | Range("AI14") = Worksheets("Year at a Glance").Range("M12") | Range("AI16") = Worksheets("Year at a Glance").Range("M14") | Range("AI17") = Worksheets("Year at a Glance").Range("M15") | Range("AI18") = Worksheets("Year at a Glance").Range("M16") | Range("AI19") = Worksheets("Year at a Glance").Range("M17") | Range("AI21") = Worksheets("Year at a Glance").Range("M19") | Range("AI22") = Worksheets("Year at a Glance").Range("M20") | Range("AI23") = Worksheets("Year at a Glance").Range("M21") | Range("AI24") = Worksheets("Year at a Glance").Range("M22") | Range("AI25") = Worksheets("Year at a Glance").Range("M23") | Range("AI26") = Worksheets("Year at a Glance").Range("M24") | Range("AI28") = Worksheets("Year at a Glance").Range("M26") | Range("AI29") = Worksheets("Year at a Glance").Range("M27") | Range("AI30") = Worksheets("Year at a Glance").Range("M28") | Range("AI31") = Worksheets("Year at a Glance").Range("M29") | Range("AI33") = Worksheets("Year at a Glance").Range("M31") | Range("AI34") = Worksheets("Year at a Glance").Range("M32") | Range("AI35") = Worksheets("Year at a Glance").Range("M33") | Range("AI38") = Worksheets("Year at a Glance").Range("M35") | Range("AI39") = Worksheets("Year at a Glance").Range("M36") | Range("AI40") = Worksheets("Year at a Glance").Range("M37") | Range("AI41") = Worksheets("Year at a Glance").Range("M38") | Range("AI42") = Worksheets("Year at a Glance").Range("M39") | | Case Is = "DECEMBER" | Range("AI7") = Worksheets("Year at a Glance").Range("N5") | Range("AI8") = Worksheets("Year at a Glance").Range("N6") | Range("AI9") = Worksheets("Year at a Glance").Range("N7") | Range("AI10") = Worksheets("Year at a Glance").Range("N8") | Range("AI11") = Worksheets("Year at a Glance").Range("N9") | Range("AI12") = Worksheets("Year at a Glance").Range("N10") | Range("AI13") = Worksheets("Year at a Glance").Range("N11") | Range("AI14") = Worksheets("Year at a Glance").Range("N12") | Range("AI16") = Worksheets("Year at a Glance").Range("N14") | Range("AI17") = Worksheets("Year at a Glance").Range("N15") | Range("AI18") = Worksheets("Year at a Glance").Range("N16") | Range("AI19") = Worksheets("Year at a Glance").Range("N17") | Range("AI21") = Worksheets("Year at a Glance").Range("N19") | Range("AI22") = Worksheets("Year at a Glance").Range("N20") | Range("AI23") = Worksheets("Year at a Glance").Range("N21") | Range("AI24") = Worksheets("Year at a Glance").Range("N22") | Range("AI25") = Worksheets("Year at a Glance").Range("N23") | Range("AI26") = Worksheets("Year at a Glance").Range("N24") | Range("AI28") = Worksheets("Year at a Glance").Range("N26") | Range("AI29") = Worksheets("Year at a Glance").Range("N27") | Range("AI30") = Worksheets("Year at a Glance").Range("N28") | Range("AI31") = Worksheets("Year at a Glance").Range("N29") | Range("AI33") = Worksheets("Year at a Glance").Range("N31") | Range("AI34") = Worksheets("Year at a Glance").Range("N32") | Range("AI35") = Worksheets("Year at a Glance").Range("N33") | Range("AI38") = Worksheets("Year at a Glance").Range("N35") | Range("AI39") = Worksheets("Year at a Glance").Range("N36") | Range("AI40") = Worksheets("Year at a Glance").Range("N37") | Range("AI41") = Worksheets("Year at a Glance").Range("N38") | Range("AI42") = Worksheets("Year at a Glance").Range("N39") | | End Select | -- | Carlee | | | "Jim Thomlinson" wrote: | | Post your code... | -- | HTH... | | Jim Thomlinson | | | "Carlee" wrote: | | Hi there, | I am using a case statement that where a condition state exists, a column of | values are populated for a specific month using: range("AI7") = | Range("J97"). I have about 30 of these statements, for twelve conditions. | | Problem: this takes a long time to execute. Is there a faster way? | -- | Carlee |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
With Application
.ScreenUpdating = False .Calculation = xlCalculationManual End With Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Worksheets("Year at a Glance").Range("C5:C39").Copy Range("AI7") Case Is = "FEBRUARY" Worksheets("Year at a Glance").Range("D5:D39").Copy Range("AI7") Case Is = "MARCH" Worksheets("Year at a Glance").Range("E5:E39").Copy Range("AI7") Case Is = "APRIL" Worksheets("Year at a Glance").Range("F5:F39").Copy Range("AI7") Case Is = "MAY" Worksheets("Year at a Glance").Range("G5:G39").Copy Range("AI7") Case Is = "JUNE" Worksheets("Year at a Glance").Range("H5:H39").Copy Range("AI7") Case Is = "JULY" Worksheets("Year at a Glance").Range("I5:I39").Copy Range("AI7") Case Is = "AUGUST" Worksheets("Year at a Glance").Range("J5:J39").Copy Range("AI7") Case Is = "SEPTEMBER" Worksheets("Year at a Glance").Range("K5:K39").Copy Range("AI7") Case Is = "OCTOBER" Worksheets("Year at a Glance").Range("L5:L39").Copy Range("AI7") Case Is = "NOVEMBERL" Worksheets("Year at a Glance").Range("M5:M39").Copy Range("AI7") Case Is = "DECEMBER" Worksheets("Year at a Glance").Range("N5:N39").Copy Range("AI7") End Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carlee" wrote in message ... Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Hmmm! Replying before the question is asked now Dave?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... If you really care about the cells that you skipped... Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
or even:
Dim iMonth%, iCol% iMonth = Month(Trim(Worksheets(destSheet).Range("B4")) & " 1/2000") iCol = 2 + iMonth With Worksheets("Year at a Glance") .Range(.Cells(5, iCol), .Cells(39, iCol)).Copy Range("AI7") End With "Bob Phillips" wrote: With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Worksheets("Year at a Glance").Range("C5:C39").Copy Range("AI7") Case Is = "FEBRUARY" Worksheets("Year at a Glance").Range("D5:D39").Copy Range("AI7") Case Is = "MARCH" Worksheets("Year at a Glance").Range("E5:E39").Copy Range("AI7") Case Is = "APRIL" Worksheets("Year at a Glance").Range("F5:F39").Copy Range("AI7") Case Is = "MAY" Worksheets("Year at a Glance").Range("G5:G39").Copy Range("AI7") Case Is = "JUNE" Worksheets("Year at a Glance").Range("H5:H39").Copy Range("AI7") Case Is = "JULY" Worksheets("Year at a Glance").Range("I5:I39").Copy Range("AI7") Case Is = "AUGUST" Worksheets("Year at a Glance").Range("J5:J39").Copy Range("AI7") Case Is = "SEPTEMBER" Worksheets("Year at a Glance").Range("K5:K39").Copy Range("AI7") Case Is = "OCTOBER" Worksheets("Year at a Glance").Range("L5:L39").Copy Range("AI7") Case Is = "NOVEMBERL" Worksheets("Year at a Glance").Range("M5:M39").Copy Range("AI7") Case Is = "DECEMBER" Worksheets("Year at a Glance").Range("N5:N39").Copy Range("AI7") End Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
OK, I didn't notice skipped cells.
Ignore my post. Carlee wrote: Range("AI14") = Range("AI16") = Dave D-C wrote: or even: Dim iMonth%, iCol% iMonth = Month(Trim(Worksheets(destSheet).Range("B4")) & " 1/2000") iCol = 2 + iMonth With Worksheets("Year at a Glance") .Range(.Cells(5, iCol), .Cells(39, iCol)).Copy Range("AI7") End With |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Ugh.
I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). I've been posting with the wrong times for at least the better part of a day! Glad you noticed and yelled! Bob Phillips wrote: Hmmm! Replying before the question is asked now Dave? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... If you really care about the cells that you skipped... Option Explicit Sub testme() Dim myDate As String Dim myMonth As Long Dim DestSheet As Worksheet Dim DestRng As Range Dim OrigRng As Range Dim gCtr As Long 'groupCounter Dim DestAddr As Variant Dim HowMany As Variant Dim FromRow As Variant Dim wks As Worksheet 'what sheet is getting these values? Set wks = ActiveSheet 'six groups starting in 'ai7, ai16, ai21, ai28, ai33, ai38 'for this many cells ' 8, 4, 6, 4, 3, 5 'starting in rows ' 5, 14, 19, 26, 31, 35 DestAddr = Array("ai7", "ai16", "ai21", "ai28", "ai33", "ai38") FromRow = Array(5, 14, 19, 26, 31, 35) HowMany = Array(8, 4, 6, 4, 3, 5) If UBound(DestAddr) = UBound(HowMany) _ And UBound(FromRow) = UBound(HowMany) Then 'whew, they match! Else MsgBox "Design error -- contact Carlee @ xxxx" Exit Sub End If myDate = Trim(Worksheets(DestSheet).Range("B4").Value) myDate = myDate & " 1, 2000" If IsDate(myDate) = False Then MsgBox "Not a month in B4 of " & DestSheet Exit Sub Else myMonth = Month(myDate) End If For gCtr = LBound(DestAddr) To UBound(DestAddr) Set DestRng = wks.Range(DestAddr).Resize(HowMany(gCtr), 1) Set OrigRng = Worksheets("Year at a Glance") _ .Cells(FromRow, "B").Offset(0, myMonth) _ .Resize(HowMany(gCtr), 1) DestRng.Value = OrigRng.Value Next gCtr End Sub Untested, but it did compile. Carlee wrote: Here is the code i am using Select Case Trim(UCase(Worksheets(destSheet).Range("B4"))) Case Is = "JANUARY" Range("AI7") = Worksheets("Year at a Glance").Range("C5") Range("AI8") = Worksheets("Year at a Glance").Range("C6") Range("AI9") = Worksheets("Year at a Glance").Range("C7") Range("AI10") = Worksheets("Year at a Glance").Range("C8") Range("AI11") = Worksheets("Year at a Glance").Range("C9") Range("AI12") = Worksheets("Year at a Glance").Range("C10") Range("AI13") = Worksheets("Year at a Glance").Range("C11") Range("AI14") = Worksheets("Year at a Glance").Range("C12") Range("AI16") = Worksheets("Year at a Glance").Range("C14") Range("AI17") = Worksheets("Year at a Glance").Range("C15") Range("AI18") = Worksheets("Year at a Glance").Range("C16") Range("AI19") = Worksheets("Year at a Glance").Range("C17") Range("AI21") = Worksheets("Year at a Glance").Range("C19") Range("AI22") = Worksheets("Year at a Glance").Range("C20") Range("AI23") = Worksheets("Year at a Glance").Range("C21") Range("AI24") = Worksheets("Year at a Glance").Range("C22") Range("AI25") = Worksheets("Year at a Glance").Range("C23") Range("AI26") = Worksheets("Year at a Glance").Range("C24") Range("AI28") = Worksheets("Year at a Glance").Range("C26") Range("AI29") = Worksheets("Year at a Glance").Range("C27") Range("AI30") = Worksheets("Year at a Glance").Range("C28") Range("AI31") = Worksheets("Year at a Glance").Range("C29") Range("AI33") = Worksheets("Year at a Glance").Range("C31") Range("AI34") = Worksheets("Year at a Glance").Range("C32") Range("AI35") = Worksheets("Year at a Glance").Range("C33") Range("AI38") = Worksheets("Year at a Glance").Range("C35") Range("AI39") = Worksheets("Year at a Glance").Range("C36") Range("AI40") = Worksheets("Year at a Glance").Range("C37") Range("AI41") = Worksheets("Year at a Glance").Range("C38") Range("AI42") = Worksheets("Year at a Glance").Range("C39") Case Is = "FEBRUARY" Range("AI7") = Worksheets("Year at a Glance").Range("D5") Range("AI8") = Worksheets("Year at a Glance").Range("D6") Range("AI9") = Worksheets("Year at a Glance").Range("D7") Range("AI10") = Worksheets("Year at a Glance").Range("D8") Range("AI11") = Worksheets("Year at a Glance").Range("D9") Range("AI12") = Worksheets("Year at a Glance").Range("D10") Range("AI13") = Worksheets("Year at a Glance").Range("D11") Range("AI14") = Worksheets("Year at a Glance").Range("D12") Range("AI16") = Worksheets("Year at a Glance").Range("D14") Range("AI17") = Worksheets("Year at a Glance").Range("D15") Range("AI18") = Worksheets("Year at a Glance").Range("D16") Range("AI19") = Worksheets("Year at a Glance").Range("D17") Range("AI21") = Worksheets("Year at a Glance").Range("D19") Range("AI22") = Worksheets("Year at a Glance").Range("D20") Range("AI23") = Worksheets("Year at a Glance").Range("D21") Range("AI24") = Worksheets("Year at a Glance").Range("D22") Range("AI25") = Worksheets("Year at a Glance").Range("D23") Range("AI26") = Worksheets("Year at a Glance").Range("D24") Range("AI28") = Worksheets("Year at a Glance").Range("D26") Range("AI29") = Worksheets("Year at a Glance").Range("D27") Range("AI30") = Worksheets("Year at a Glance").Range("D28") Range("AI31") = Worksheets("Year at a Glance").Range("D29") Range("AI33") = Worksheets("Year at a Glance").Range("D31") Range("AI34") = Worksheets("Year at a Glance").Range("D32") Range("AI35") = Worksheets("Year at a Glance").Range("D33") Range("AI38") = Worksheets("Year at a Glance").Range("D35") Range("AI39") = Worksheets("Year at a Glance").Range("D36") Range("AI40") = Worksheets("Year at a Glance").Range("D37") Range("AI41") = Worksheets("Year at a Glance").Range("D38") Range("AI42") = Worksheets("Year at a Glance").Range("D39") Case Is = "MARCH" Range("AI7") = Worksheets("Year at a Glance").Range("E5") Range("AI8") = Worksheets("Year at a Glance").Range("E6") Range("AI9") = Worksheets("Year at a Glance").Range("E7") Range("AI10") = Worksheets("Year at a Glance").Range("E8") Range("AI11") = Worksheets("Year at a Glance").Range("E9") Range("AI12") = Worksheets("Year at a Glance").Range("E10") Range("AI13") = Worksheets("Year at a Glance").Range("E11") Range("AI14") = Worksheets("Year at a Glance").Range("E12") Range("AI16") = Worksheets("Year at a Glance").Range("E14") Range("AI17") = Worksheets("Year at a Glance").Range("E15") Range("AI18") = Worksheets("Year at a Glance").Range("E16") Range("AI19") = Worksheets("Year at a Glance").Range("E17") Range("AI21") = Worksheets("Year at a Glance").Range("E19") Range("AI22") = Worksheets("Year at a Glance").Range("E20") Range("AI23") = Worksheets("Year at a Glance").Range("E21") Range("AI24") = Worksheets("Year at a Glance").Range("E22") Range("AI25") = Worksheets("Year at a Glance").Range("E23") Range("AI26") = Worksheets("Year at a Glance").Range("E24") Range("AI28") = Worksheets("Year at a Glance").Range("E26") Range("AI29") = Worksheets("Year at a Glance").Range("E27") Range("AI30") = Worksheets("Year at a Glance").Range("E28") Range("AI31") = Worksheets("Year at a Glance").Range("E29") Range("AI33") = Worksheets("Year at a Glance").Range("E31") Range("AI34") = Worksheets("Year at a Glance").Range("E32") Range("AI35") = Worksheets("Year at a Glance").Range("E33") Range("AI38") = Worksheets("Year at a Glance").Range("E35") Range("AI39") = Worksheets("Year at a Glance").Range("E36") Range("AI40") = Worksheets("Year at a Glance").Range("E37") Range("AI41") = Worksheets("Year at a Glance").Range("E38") Range("AI42") = Worksheets("Year at a Glance").Range("E39") Case Is = "APRIL" Range("AI7") = Worksheets("Year at a Glance").Range("F5") Range("AI8") = Worksheets("Year at a Glance").Range("F6") Range("AI9") = Worksheets("Year at a Glance").Range("F7") Range("AI10") = Worksheets("Year at a Glance").Range("F8") Range("AI11") = Worksheets("Year at a Glance").Range("F9") Range("AI12") = Worksheets("Year at a Glance").Range("F10") Range("AI13") = Worksheets("Year at a Glance").Range("F11") Range("AI14") = Worksheets("Year at a Glance").Range("F12") Range("AI16") = Worksheets("Year at a Glance").Range("F14") Range("AI17") = Worksheets("Year at a Glance").Range("F15") Range("AI18") = Worksheets("Year at a Glance").Range("F16") Range("AI19") = Worksheets("Year at a Glance").Range("F17") Range("AI21") = Worksheets("Year at a Glance").Range("F19") Range("AI22") = Worksheets("Year at a Glance").Range("F20") Range("AI23") = Worksheets("Year at a Glance").Range("F21") Range("AI24") = Worksheets("Year at a Glance").Range("F22") Range("AI25") = Worksheets("Year at a Glance").Range("F23") Range("AI26") = Worksheets("Year at a Glance").Range("F24") Range("AI28") = Worksheets("Year at a Glance").Range("F26") Range("AI29") = Worksheets("Year at a Glance").Range("F27") Range("AI30") = Worksheets("Year at a Glance").Range("F28") Range("AI31") = Worksheets("Year at a Glance").Range("F29") Range("AI33") = Worksheets("Year at a Glance").Range("F31") Range("AI34") = Worksheets("Year at a Glance").Range("F32") Range("AI35") = Worksheets("Year at a Glance").Range("F33") Range("AI38") = Worksheets("Year at a Glance").Range("F35") Range("AI39") = Worksheets("Year at a Glance").Range("F36") Range("AI40") = Worksheets("Year at a Glance").Range("F37") Range("AI41") = Worksheets("Year at a Glance").Range("F38") Range("AI42") = Worksheets("Year at a Glance").Range("F39") Case Is = "MAY" Range("AI7") = Worksheets("Year at a Glance").Range("G5") Range("AI8") = Worksheets("Year at a Glance").Range("G6") Range("AI9") = Worksheets("Year at a Glance").Range("G7") Range("AI10") = Worksheets("Year at a Glance").Range("G8") Range("AI11") = Worksheets("Year at a Glance").Range("G9") Range("AI12") = Worksheets("Year at a Glance").Range("G10") Range("AI13") = Worksheets("Year at a Glance").Range("G11") Range("AI14") = Worksheets("Year at a Glance").Range("G12") Range("AI16") = Worksheets("Year at a Glance").Range("G14") Range("AI17") = Worksheets("Year at a Glance").Range("G15") Range("AI18") = Worksheets("Year at a Glance").Range("G16") Range("AI19") = Worksheets("Year at a Glance").Range("G17") Range("AI21") = Worksheets("Year at a Glance").Range("G19") Range("AI22") = Worksheets("Year at a Glance").Range("G20") Range("AI23") = Worksheets("Year at a Glance").Range("G21") Range("AI24") = Worksheets("Year at a Glance").Range("G22") Range("AI25") = Worksheets("Year at a Glance").Range("G23") Range("AI26") = Worksheets("Year at a Glance").Range("G24") Range("AI28") = Worksheets("Year at a Glance").Range("G26") Range("AI29") = Worksheets("Year at a Glance").Range("G27") Range("AI30") = Worksheets("Year at a Glance").Range("G28") Range("AI31") = Worksheets("Year at a Glance").Range("G29") Range("AI33") = Worksheets("Year at a Glance").Range("G31") Range("AI34") = Worksheets("Year at a Glance").Range("G32") Range("AI35") = Worksheets("Year at a Glance").Range("G33") Range("AI38") = Worksheets("Year at a Glance").Range("G35") Range("AI39") = Worksheets("Year at a Glance").Range("G36") Range("AI40") = Worksheets("Year at a Glance").Range("G37") Range("AI41") = Worksheets("Year at a Glance").Range("G38") Range("AI42") = Worksheets("Year at a Glance").Range("G39") Case Is = "JUNE" Range("AI7") = Worksheets("Year at a Glance").Range("H5") Range("AI8") = Worksheets("Year at a Glance").Range("H6") Range("AI9") = Worksheets("Year at a Glance").Range("H7") Range("AI10") = Worksheets("Year at a Glance").Range("H8") Range("AI11") = Worksheets("Year at a Glance").Range("H9") Range("AI12") = Worksheets("Year at a Glance").Range("H10") Range("AI13") = Worksheets("Year at a Glance").Range("H11") Range("AI14") = Worksheets("Year at a Glance").Range("H12") Range("AI16") = Worksheets("Year at a Glance").Range("H14") Range("AI17") = Worksheets("Year at a Glance").Range("H15") Range("AI18") = Worksheets("Year at a Glance").Range("H16") Range("AI19") = Worksheets("Year at a Glance").Range("H17") Range("AI21") = Worksheets("Year at a Glance").Range("H19") Range("AI22") = Worksheets("Year at a Glance").Range("H20") Range("AI23") = Worksheets("Year at a Glance").Range("H21") Range("AI24") = Worksheets("Year at a Glance").Range("H22") Range("AI25") = Worksheets("Year at a Glance").Range("H23") Range("AI26") = Worksheets("Year at a Glance").Range("H24") Range("AI28") = Worksheets("Year at a Glance").Range("H26") Range("AI29") = Worksheets("Year at a Glance").Range("H27") Range("AI30") = Worksheets("Year at a Glance").Range("H28") Range("AI31") = Worksheets("Year at a Glance").Range("H29") Range("AI33") = Worksheets("Year at a Glance").Range("H31") Range("AI34") = Worksheets("Year at a Glance").Range("H32") Range("AI35") = Worksheets("Year at a Glance").Range("H33") Range("AI38") = Worksheets("Year at a Glance").Range("H35") Range("AI39") = Worksheets("Year at a Glance").Range("H36") Range("AI40") = Worksheets("Year at a Glance").Range("H37") Range("AI41") = Worksheets("Year at a Glance").Range("H38") Range("AI42") = Worksheets("Year at a Glance").Range("H39") Case Is = "JULY" Range("AI7") = Worksheets("Year at a Glance").Range("I5") Range("AI8") = Worksheets("Year at a Glance").Range("I6") Range("AI9") = Worksheets("Year at a Glance").Range("I7") Range("AI10") = Worksheets("Year at a Glance").Range("I8") Range("AI11") = Worksheets("Year at a Glance").Range("I9") Range("AI12") = Worksheets("Year at a Glance").Range("I10") Range("AI13") = Worksheets("Year at a Glance").Range("I11") Range("AI14") = Worksheets("Year at a Glance").Range("I12") Range("AI16") = Worksheets("Year at a Glance").Range("I14") Range("AI17") = Worksheets("Year at a Glance").Range("I15") Range("AI18") = Worksheets("Year at a Glance").Range("I16") Range("AI19") = Worksheets("Year at a Glance").Range("I17") Range("AI21") = Worksheets("Year at a Glance").Range("I19") Range("AI22") = Worksheets("Year at a Glance").Range("I20") Range("AI23") = Worksheets("Year at a Glance").Range("I21") Range("AI24") = Worksheets("Year at a Glance").Range("I22") Range("AI25") = Worksheets("Year at a Glance").Range("I23") Range("AI26") = Worksheets("Year at a Glance").Range("I24") Range("AI28") = Worksheets("Year at a Glance").Range("I26") Range("AI29") = Worksheets("Year at a Glance").Range("I27") Range("AI30") = Worksheets("Year at a Glance").Range("I28") Range("AI31") = Worksheets("Year at a Glance").Range("I29") Range("AI33") = Worksheets("Year at a Glance").Range("I31") Range("AI34") = Worksheets("Year at a Glance").Range("I32") Range("AI35") = Worksheets("Year at a Glance").Range("I33") Range("AI38") = Worksheets("Year at a Glance").Range("I35") Range("AI39") = Worksheets("Year at a Glance").Range("I36") Range("AI40") = Worksheets("Year at a Glance").Range("I37") Range("AI41") = Worksheets("Year at a Glance").Range("I38") Range("AI42") = Worksheets("Year at a Glance").Range("I39") Case Is = "AUGUST" Range("AI7") = Worksheets("Year at a Glance").Range("J5") Range("AI8") = Worksheets("Year at a Glance").Range("J6") Range("AI9") = Worksheets("Year at a Glance").Range("J7") Range("AI10") = Worksheets("Year at a Glance").Range("J8") Range("AI11") = Worksheets("Year at a Glance").Range("J9") Range("AI12") = Worksheets("Year at a Glance").Range("J10") Range("AI13") = Worksheets("Year at a Glance").Range("J11") Range("AI14") = Worksheets("Year at a Glance").Range("J12") Range("AI16") = Worksheets("Year at a Glance").Range("J14") Range("AI17") = Worksheets("Year at a Glance").Range("J15") Range("AI18") = Worksheets("Year at a Glance").Range("J16") Range("AI19") = Worksheets("Year at a Glance").Range("J17") Range("AI21") = Worksheets("Year at a Glance").Range("J19") Range("AI22") = Worksheets("Year at a Glance").Range("J20") Range("AI23") = Worksheets("Year at a Glance").Range("J21") Range("AI24") = Worksheets("Year at a Glance").Range("J22") Range("AI25") = Worksheets("Year at a Glance").Range("J23") Range("AI26") = Worksheets("Year at a Glance").Range("J24") Range("AI28") = Worksheets("Year at a Glance").Range("J26") Range("AI29") = Worksheets("Year at a Glance").Range("J27") Range("AI30") = Worksheets("Year at a Glance").Range("J28") Range("AI31") = Worksheets("Year at a Glance").Range("J29") Range("AI33") = Worksheets("Year at a Glance").Range("J31") Range("AI34") = Worksheets("Year at a Glance").Range("J32") Range("AI35") = Worksheets("Year at a Glance").Range("J33") Range("AI38") = Worksheets("Year at a Glance").Range("J35") Range("AI39") = Worksheets("Year at a Glance").Range("J36") Range("AI40") = Worksheets("Year at a Glance").Range("J37") Range("AI41") = Worksheets("Year at a Glance").Range("J38") Range("AI42") = Worksheets("Year at a Glance").Range("J39") Case Is = "SEPTEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("K5") Range("AI8") = Worksheets("Year at a Glance").Range("K6") Range("AI9") = Worksheets("Year at a Glance").Range("K7") Range("AI10") = Worksheets("Year at a Glance").Range("K8") Range("AI11") = Worksheets("Year at a Glance").Range("K9") Range("AI12") = Worksheets("Year at a Glance").Range("K10") Range("AI13") = Worksheets("Year at a Glance").Range("K11") Range("AI14") = Worksheets("Year at a Glance").Range("K12") Range("AI16") = Worksheets("Year at a Glance").Range("K14") Range("AI17") = Worksheets("Year at a Glance").Range("K15") Range("AI18") = Worksheets("Year at a Glance").Range("K16") Range("AI19") = Worksheets("Year at a Glance").Range("K17") Range("AI21") = Worksheets("Year at a Glance").Range("K19") Range("AI22") = Worksheets("Year at a Glance").Range("K20") Range("AI23") = Worksheets("Year at a Glance").Range("K21") Range("AI24") = Worksheets("Year at a Glance").Range("K22") Range("AI25") = Worksheets("Year at a Glance").Range("K23") Range("AI26") = Worksheets("Year at a Glance").Range("K24") Range("AI28") = Worksheets("Year at a Glance").Range("K26") Range("AI29") = Worksheets("Year at a Glance").Range("K27") Range("AI30") = Worksheets("Year at a Glance").Range("K28") Range("AI31") = Worksheets("Year at a Glance").Range("K29") Range("AI33") = Worksheets("Year at a Glance").Range("K31") Range("AI34") = Worksheets("Year at a Glance").Range("K32") Range("AI35") = Worksheets("Year at a Glance").Range("K33") Range("AI38") = Worksheets("Year at a Glance").Range("K35") Range("AI39") = Worksheets("Year at a Glance").Range("K36") Range("AI40") = Worksheets("Year at a Glance").Range("K37") Range("AI41") = Worksheets("Year at a Glance").Range("K38") Range("AI42") = Worksheets("Year at a Glance").Range("K39") Case Is = "OCTOBER" Range("AI7") = Worksheets("Year at a Glance").Range("L5") Range("AI8") = Worksheets("Year at a Glance").Range("L6") Range("AI9") = Worksheets("Year at a Glance").Range("L7") Range("AI10") = Worksheets("Year at a Glance").Range("L8") Range("AI11") = Worksheets("Year at a Glance").Range("L9") Range("AI12") = Worksheets("Year at a Glance").Range("L10") Range("AI13") = Worksheets("Year at a Glance").Range("L11") Range("AI14") = Worksheets("Year at a Glance").Range("L12") Range("AI16") = Worksheets("Year at a Glance").Range("L14") Range("AI17") = Worksheets("Year at a Glance").Range("L15") Range("AI18") = Worksheets("Year at a Glance").Range("L16") Range("AI19") = Worksheets("Year at a Glance").Range("L17") Range("AI21") = Worksheets("Year at a Glance").Range("L19") Range("AI22") = Worksheets("Year at a Glance").Range("L20") Range("AI23") = Worksheets("Year at a Glance").Range("L21") Range("AI24") = Worksheets("Year at a Glance").Range("L22") Range("AI25") = Worksheets("Year at a Glance").Range("L23") Range("AI26") = Worksheets("Year at a Glance").Range("L24") Range("AI28") = Worksheets("Year at a Glance").Range("L26") Range("AI29") = Worksheets("Year at a Glance").Range("L27") Range("AI30") = Worksheets("Year at a Glance").Range("L28") Range("AI31") = Worksheets("Year at a Glance").Range("L29") Range("AI33") = Worksheets("Year at a Glance").Range("L31") Range("AI34") = Worksheets("Year at a Glance").Range("L32") Range("AI35") = Worksheets("Year at a Glance").Range("L33") Range("AI38") = Worksheets("Year at a Glance").Range("L35") Range("AI39") = Worksheets("Year at a Glance").Range("L36") Range("AI40") = Worksheets("Year at a Glance").Range("L37") Range("AI41") = Worksheets("Year at a Glance").Range("L38") Range("AI42") = Worksheets("Year at a Glance").Range("L39") Case Is = "NOVEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("M5") Range("AI8") = Worksheets("Year at a Glance").Range("M6") Range("AI9") = Worksheets("Year at a Glance").Range("M7") Range("AI10") = Worksheets("Year at a Glance").Range("M8") Range("AI11") = Worksheets("Year at a Glance").Range("M9") Range("AI12") = Worksheets("Year at a Glance").Range("M10") Range("AI13") = Worksheets("Year at a Glance").Range("M11") Range("AI14") = Worksheets("Year at a Glance").Range("M12") Range("AI16") = Worksheets("Year at a Glance").Range("M14") Range("AI17") = Worksheets("Year at a Glance").Range("M15") Range("AI18") = Worksheets("Year at a Glance").Range("M16") Range("AI19") = Worksheets("Year at a Glance").Range("M17") Range("AI21") = Worksheets("Year at a Glance").Range("M19") Range("AI22") = Worksheets("Year at a Glance").Range("M20") Range("AI23") = Worksheets("Year at a Glance").Range("M21") Range("AI24") = Worksheets("Year at a Glance").Range("M22") Range("AI25") = Worksheets("Year at a Glance").Range("M23") Range("AI26") = Worksheets("Year at a Glance").Range("M24") Range("AI28") = Worksheets("Year at a Glance").Range("M26") Range("AI29") = Worksheets("Year at a Glance").Range("M27") Range("AI30") = Worksheets("Year at a Glance").Range("M28") Range("AI31") = Worksheets("Year at a Glance").Range("M29") Range("AI33") = Worksheets("Year at a Glance").Range("M31") Range("AI34") = Worksheets("Year at a Glance").Range("M32") Range("AI35") = Worksheets("Year at a Glance").Range("M33") Range("AI38") = Worksheets("Year at a Glance").Range("M35") Range("AI39") = Worksheets("Year at a Glance").Range("M36") Range("AI40") = Worksheets("Year at a Glance").Range("M37") Range("AI41") = Worksheets("Year at a Glance").Range("M38") Range("AI42") = Worksheets("Year at a Glance").Range("M39") Case Is = "DECEMBER" Range("AI7") = Worksheets("Year at a Glance").Range("N5") Range("AI8") = Worksheets("Year at a Glance").Range("N6") Range("AI9") = Worksheets("Year at a Glance").Range("N7") Range("AI10") = Worksheets("Year at a Glance").Range("N8") Range("AI11") = Worksheets("Year at a Glance").Range("N9") Range("AI12") = Worksheets("Year at a Glance").Range("N10") Range("AI13") = Worksheets("Year at a Glance").Range("N11") Range("AI14") = Worksheets("Year at a Glance").Range("N12") Range("AI16") = Worksheets("Year at a Glance").Range("N14") Range("AI17") = Worksheets("Year at a Glance").Range("N15") Range("AI18") = Worksheets("Year at a Glance").Range("N16") Range("AI19") = Worksheets("Year at a Glance").Range("N17") Range("AI21") = Worksheets("Year at a Glance").Range("N19") Range("AI22") = Worksheets("Year at a Glance").Range("N20") Range("AI23") = Worksheets("Year at a Glance").Range("N21") Range("AI24") = Worksheets("Year at a Glance").Range("N22") Range("AI25") = Worksheets("Year at a Glance").Range("N23") Range("AI26") = Worksheets("Year at a Glance").Range("N24") Range("AI28") = Worksheets("Year at a Glance").Range("N26") Range("AI29") = Worksheets("Year at a Glance").Range("N27") Range("AI30") = Worksheets("Year at a Glance").Range("N28") Range("AI31") = Worksheets("Year at a Glance").Range("N29") Range("AI33") = Worksheets("Year at a Glance").Range("N31") Range("AI34") = Worksheets("Year at a Glance").Range("N32") Range("AI35") = Worksheets("Year at a Glance").Range("N33") Range("AI38") = Worksheets("Year at a Glance").Range("N35") Range("AI39") = Worksheets("Year at a Glance").Range("N36") Range("AI40") = Worksheets("Year at a Glance").Range("N37") Range("AI41") = Worksheets("Year at a Glance").Range("N38") Range("AI42") = Worksheets("Year at a Glance").Range("N39") End Select -- Carlee "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "Carlee" wrote: Hi there, I am using a case statement that where a condition state exists, a column of values are populated for a specific month using: range("AI7") = Range("J97"). I have about 30 of these statements, for twelve conditions. Problem: this takes a long time to execute. Is there a faster way? -- Carlee -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
"Dave Peterson" wrote in message ... Ugh. I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). And there I was thinking it was psychic. I've been posting with the wrong times for at least the better part of a day! You don't think I read your posts (normally) do you? I said some time ago that I had to stop reading your posts after RD said I was starting to sound like you <bg. Glad you noticed and yelled! Peeved me that your post popped up above mine half a day later than mine <ebg |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
It may have been more psyco than psychic--but what's a couple of letters???
And from now on, I'll be changing the time to just a few minutes before your posts! <gd&r Bob Phillips wrote: "Dave Peterson" wrote in message ... Ugh. I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). And there I was thinking it was psychic. I've been posting with the wrong times for at least the better part of a day! You don't think I read your posts (normally) do you? I said some time ago that I had to stop reading your posts after RD said I was starting to sound like you <bg. Glad you noticed and yelled! Peeved me that your post popped up above mine half a day later than mine <ebg -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
or psycho
(darn spelling errors!) Dave Peterson wrote: It may have been more psyco than psychic--but what's a couple of letters??? And from now on, I'll be changing the time to just a few minutes before your posts! <gd&r <<snipped |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
I thought you had been doing that for the past 5 years <gGg
BTW <gd&r, don't think I know that one. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... It may have been more psyco than psychic--but what's a couple of letters??? And from now on, I'll be changing the time to just a few minutes before your posts! <gd&r Bob Phillips wrote: "Dave Peterson" wrote in message ... Ugh. I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). And there I was thinking it was psychic. I've been posting with the wrong times for at least the better part of a day! You don't think I read your posts (normally) do you? I said some time ago that I had to stop reading your posts after RD said I was starting to sound like you <bg. Glad you noticed and yelled! Peeved me that your post popped up above mine half a day later than mine <ebg -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
Grin, duck and run.
(If it wasn't soooo much work, I would have changed the time for this post!) Bob Phillips wrote: I thought you had been doing that for the past 5 years <gGg BTW <gd&r, don't think I know that one. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... It may have been more psyco than psychic--but what's a couple of letters??? And from now on, I'll be changing the time to just a few minutes before your posts! <gd&r Bob Phillips wrote: "Dave Peterson" wrote in message ... Ugh. I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). And there I was thinking it was psychic. I've been posting with the wrong times for at least the better part of a day! You don't think I read your posts (normally) do you? I said some time ago that I had to stop reading your posts after RD said I was starting to sound like you <bg. Glad you noticed and yelled! Peeved me that your post popped up above mine half a day later than mine <ebg -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a faster way
LOL!
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... Grin, duck and run. (If it wasn't soooo much work, I would have changed the time for this post!) Bob Phillips wrote: I thought you had been doing that for the past 5 years <gGg BTW <gd&r, don't think I know that one. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... It may have been more psyco than psychic--but what's a couple of letters??? And from now on, I'll be changing the time to just a few minutes before your posts! <gd&r Bob Phillips wrote: "Dave Peterson" wrote in message ... Ugh. I was screwing around with the time format in the control panel and removed the AM/PM indicators. The next time I set the time, it screwed up (confused by the lack of AM/PM???). And there I was thinking it was psychic. I've been posting with the wrong times for at least the better part of a day! You don't think I read your posts (normally) do you? I said some time ago that I had to stop reading your posts after RD said I was starting to sound like you <bg. Glad you noticed and yelled! Peeved me that your post popped up above mine half a day later than mine <ebg -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can this be done faster? | Excel Discussion (Misc queries) | |||
| Any way to do this faster? | Excel Programming | |||
| needs to run faster | Excel Programming | |||
| Faster way to do this? | Excel Programming | |||
| Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Linear Mode
