Is there a faster way


D

Dave Peterson

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.
 
Ad

Advertisements

C

Carlee

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?
 
C

Carlee

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
 
J

Jim Rech

Range("AI7:AI42").Value = Worksheets("Year at a
Glance").Range("C5:C39").Value



--
Jim
| 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
 
B

Bob Phillips

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)
 
Ad

Advertisements

B

Bob Phillips

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)
 
D

Dave D-C

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
 
D

Dave D-C

OK, I didn't notice skipped cells.
Ignore my post.

Range("AI14") =
Range("AI16") =
 
D

Dave Peterson

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 said:
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)
 
B

Bob Phillips

Dave Peterson said:
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
Glad you noticed and yelled!


Peeved me that your post popped up above mine half a day later than mine
<ebg>
 
Ad

Advertisements

D

Dave Peterson

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>
 
D

Dave Peterson

or psycho
(darn spelling errors!)

Dave said:
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>>
 
B

Bob Phillips

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)
 
D

Dave Peterson

Grin, duck and run.

(If it wasn't soooo much work, I would have changed the time for this post!)
 
Ad

Advertisements

B

Bob Phillips

LOL!

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top