 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / December 2007Is 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?  SignatureCarlee Post your code... SignatureHTH... Jim Thomlinson > 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? 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 SignatureCarlee > Post your code... > [quoted text clipped - 4 lines] > > > > Problem: this takes a long time to execute. 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. > Here is the code i am using > [quoted text clipped - 398 lines] > > > -- > > > Carlee SignatureDave Peterson Hmmm! Replying before the question is asked now Dave? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > If you really care about the cells that you skipped... > [quoted text clipped - 462 lines] >> > > -- >> > > Carlee 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! > Hmmm! Replying before the question is asked now Dave? > [quoted text clipped - 476 lines] > > > > Dave Peterson SignatureDave Peterson > Ugh. > [quoted text clipped - 3 lines] > 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> 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> > > Ugh. > > [quoted text clipped - 17 lines] > Peeved me that your post popped up above mine half a day later than mine > <ebg> SignatureDave Peterson or psycho (darn spelling errors!) > 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>> I thought you had been doing that for the past 5 years <gGg> BTW <gd&r>, don't think I know that one. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > It may have been more psyco than psychic--but what's a couple of > letters??? [quoted text clipped - 27 lines] >> Peeved me that your post popped up above mine half a day later than mine >> <ebg> Grin, duck and run. (If it wasn't soooo much work, I would have changed the time for this post!) > I thought you had been doing that for the past 5 years <gGg> > [quoted text clipped - 43 lines] > > > > Dave Peterson SignatureDave Peterson LOL! Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Grin, duck and run. > [quoted text clipped - 52 lines] >> > >> > Dave Peterson Range("AI7:AI42").Value = Worksheets("Year at a Glance").Range("C5:C39").Value SignatureJim | Here is the code i am using | [quoted text clipped - 390 lines] | > > | > > Problem: this takes a long time to execute. 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 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Here is the code i am using > [quoted text clipped - 392 lines] >> > >> > Problem: this takes a long time to execute. 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 > With Application > .ScreenUpdating = False [quoted text clipped - 32 lines] > .Calculation = xlCalculationAutomatic > End With OK, I didn't notice skipped cells. Ignore my post. Carlee <Carlee@discussions.microsoft.com> wrote: Range("AI14") = Range("AI16") = >or even: > Dim iMonth%, iCol% [quoted text clipped - 3 lines] > .Range(.Cells(5, iCol), .Cells(39, iCol)).Copy Range("AI7") > End With |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.