Excel 2003 VB Code Help Needed

E

elliott740

Board Regular
Joined
Mar 14, 2008
Messages
52
How can I optimize / reuse sections of this code? Ex: See all of the Worksheet lines within the For Next Loop. I am using this same snipit of code multiple time but do not know how to save this code and call it like a routine so I don't have to keep typing the same thing over and over again. My code is now so big that it will not compile (> 64k)

Please Help :confused:

<CENTER><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - Book1</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value="" selected>A1</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value="Private Sub Refresh_Click()" name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">

</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Private Sub Refresh_Click()</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BwCol = 102 'Current Year January Sales (BW Rpt - Summary) & (BW Rpt - Detail1) tabs</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">For BSegLoopCounter = 22 To 72 'Nbr of Segments from Cover Page</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">For LoopCounter = 1 To 100 'Nbr of Rows in BW Rpt - Summary</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">If Worksheets("Cover Page").Cells(BSegLoopCounter, 4).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, 1).Value Then</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol).Value 'CMth Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol + 1).Value 'CMth Budget</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol - 48).Value 'CMth LY Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 12).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'QTD Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 13).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'QTD Budget</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>11</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 14).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'QTD LY Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>12</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 19).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'YTD Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>13</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 20).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'YTD Budget</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>14</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 21).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'YTD LY Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>15</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">For AA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>16</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA).Value 'CY Annual Budget</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>17</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>18</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>19</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Next AA</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>20</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">RowCounter = RowCounter + 1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>21</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Exit For</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>22</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">End If</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>23</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">If (Worksheets("BW Rpt - Summary").Cells(LoopCounter, 1).Value = "") And (Worksheets("BW Rpt - Summary").Cells(LoopCounter + 1, 1).Value = "") Then</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>24</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Exit For</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>25</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">End If</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>26</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Next LoopCounter</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>27</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">If Worksheets("Cover Page").Cells(BSegLoopCounter + 1, 4).Value = "" Then</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>28</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Exit For</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>29</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">End If</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>30</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Next BSegLoopCounter</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left>Sheet1</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi elliott,
You can store repeated code in a module, and from the button's click event use a Call statement.

For example:
On Sheet1 I have a command button:
Code: 
Private Sub CommandButton1_Click()
    Call refreshMe
End Sub
On Sheet2 I have another command button:
Code: 
Private Sub CommandButton2_Click()
    Call refreshMe
End Sub
In a module, I have:
Code: 
Sub refreshMe()
    ActiveSheet.Range("A1").Value = "Bob - Tom"
End Sub
This is an over-simplified version, but it will at least show you what can be done to avoid re-writing code.
 
Upvote 0
elliott740,

Please post your code using code tags.

At the beginning of your posted code, enter the following without the four double quote marks:
"["code"]"


'Your code goes here.


At the end of your posted code, enter the following without the four double quote marks:
"["/code"]"


Have a great day,
Stan
 
Upvote 0
Code: 
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Sheet5"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Private Sub Refresh_Click()
    Worksheets("Dollar Sales (New)").Range("E8:G9").Clear
    Worksheets("Dollar Sales (New)").Range("L8:N9").Clear
    Worksheets("Dollar Sales (New)").Range("S8:U9").Clear
    Worksheets("Dollar Sales (New)").Range("Z8:AB9").Clear
    Worksheets("Dollar Sales (New)").Range("E12:G24").Clear
    Worksheets("Dollar Sales (New)").Range("L12:N24").Clear
    Worksheets("Dollar Sales (New)").Range("S12:U24").Clear
    Worksheets("Dollar Sales (New)").Range("Z12:AB24").Clear
    Worksheets("Dollar Sales (New)").Range("E29:G38").Clear
    Worksheets("Dollar Sales (New)").Range("L29:N38").Clear
    Worksheets("Dollar Sales (New)").Range("S29:U38").Clear
    Worksheets("Dollar Sales (New)").Range("Z29:AB38").Clear
    Worksheets("Dollar Sales (New)").Range("E43:G52").Clear
    Worksheets("Dollar Sales (New)").Range("L43:N52").Clear
    Worksheets("Dollar Sales (New)").Range("S43:U52").Clear
    Worksheets("Dollar Sales (New)").Range("Z43:AB52").Clear
    Dim Mth As Integer
    Dim BwCol As Integer
    Dim RowCounter As Integer
    Mth = Worksheets("Cover Page").Cells(2, 3).Value
    RowCounter = 8
    Select Case Mth
       Case "1" 'Jan
          BwCol = 102 'Current Year January Sales (BW Rpt - Summary) & (BW Rpt - Detail1) tabs
          For BSegLoopCounter = 22 To 72 'Nbr of Segments from Cover Page
             For LoopCounter = 1 To 100 'Nbr of Rows in BW Rpt - Summary
                If Worksheets("Cover Page").Cells(BSegLoopCounter, 4).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, 1).Value Then
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol).Value 'CMth Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol + 1).Value 'CMth Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value = Worksheets("BW Rpt - Summary").Cells(LoopCounter, BwCol - 48).Value 'CMth LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 12).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'QTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 13).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'QTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 14).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value  'QTD LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 19).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'YTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 20).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'YTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 21).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value  'YTD LY Sales
                   For AA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA).Value  'CY Annual Budget
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales
                   Next AA
                   RowCounter = RowCounter + 1
                   Exit For
                End If
                If (Worksheets("BW Rpt - Summary").Cells(LoopCounter, 1).Value = "") And (Worksheets("BW Rpt - Summary").Cells(LoopCounter + 1, 1).Value = "") Then
                   Exit For
                End If
             Next LoopCounter
             If Worksheets("Cover Page").Cells(BSegLoopCounter + 1, 4).Value = "" Then
                   Exit For
             End If
          Next BSegLoopCounter
'
          RowCounter = 12
'
          For BSegLoopCounter = 3 To 53 'Nbr of Top Customers (Overall)
             For LoopCounter = 1 To 100 'Nbr of Rows in BW Rpt - Detail1
                If Worksheets("Cover Page").Cells(BSegLoopCounter, 8).Value = Worksheets("BW Rpt - Detail1").Cells(LoopCounter, 1).Value Then
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value = Worksheets("BW Rpt - Detail1").Cells(LoopCounter, BwCol).Value 'CMth Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value = Worksheets("BW Rpt - Detail1").Cells(LoopCounter, BwCol + 1).Value 'CMth Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value = Worksheets("BW Rpt - Detail1").Cells(LoopCounter, BwCol - 48).Value  'CMth LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 12).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'QTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 13).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'QTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 14).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value  'QTD LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 19).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'YTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 20).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'YTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 21).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value  'YTD LY Sales
                   For AA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Detail1").Cells(LoopCounter, AA).Value  'CY Annual Budget
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Detail1").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Detail1").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales
                   Next AA
                   RowCounter = RowCounter + 1
                   Exit For
                End If
                If (Worksheets("BW Rpt - Detail1").Cells(LoopCounter, 1).Value = "") And (Worksheets("BW Rpt - Detail1").Cells(LoopCounter + 1, 1).Value = "") Then
                   Exit For
                End If
             Next LoopCounter
             If Worksheets("Cover Page").Cells(BSegLoopCounter + 1, 8).Value = "" Then
                   Exit For
             End If
          Next BSegLoopCounter
'
          BwCol = 104 'Current Year January Sales (BW Rpt - Detail2) tab
          RowCounter = 29
'
          For BSegLoopCounter = 3 To 53 'Nbr of Top Customers (Display Board)
             For LoopCounter = 1 To 100 'Nbr of Rows in BW Rpt - Detail2
                If Worksheets("Cover Page").Cells(BSegLoopCounter, 10).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, 1).Value Then
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol).Value 'CMth Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol + 1).Value 'CMth Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol - 48).Value 'CMth LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 12).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'QTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 13).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'QTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 14).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'QTD LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 19).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'YTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 20).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'YTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 21).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'YTD LY Sales
                   For AA = 105 To 149 Step 4 'Loop to Capture 12 Months (Annual Data)
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA).Value  'CY Annual Budget
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales
                   Next AA
                   RowCounter = RowCounter + 1
                   Exit For
                End If
                If (Worksheets("BW Rpt - Detail2").Cells(LoopCounter, 1).Value = "") And (Worksheets("BW Rpt - Detail2").Cells(LoopCounter + 1, 1).Value = "") Then
                   Exit For
                End If
             Next LoopCounter
             If Worksheets("Cover Page").Cells(BSegLoopCounter + 1, 10).Value = "" Then
                   Exit For
             End If
          Next BSegLoopCounter
'
          RowCounter = 43
'
          For BSegLoopCounter = 3 To 53 'Nbr of Top Customers (Foam Board)
             For LoopCounter = 1 To 100 'Nbr of Rows in BW Rpt - Detail2
                If Worksheets("Cover Page").Cells(BSegLoopCounter, 12).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, 1).Value Then
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol).Value 'CMth Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol + 1).Value 'CMth Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value = Worksheets("BW Rpt - Detail2").Cells(LoopCounter, BwCol - 48).Value 'CMth LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 12).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'QTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 13).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'QTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 14).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'QTD LY Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 19).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 5).Value 'YTD Sales
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 20).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 6).Value 'YTD Budget
                   Worksheets("Dollar Sales (New)").Cells(RowCounter, 21).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 7).Value 'YTD LY Sales
                   For AA = 105 To 149 Step 4 'Loop to Capture 12 Months (Annual Data)
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA).Value  'CY Annual Budget
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales
                      Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Detail2").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales
                   Next AA
                   RowCounter = RowCounter + 1
                   Exit For
                End If
                If (Worksheets("BW Rpt - Detail2").Cells(LoopCounter, 1).Value = "") And (Worksheets("BW Rpt - Detail2").Cells(LoopCounter + 1, 1).Value = "") Then
                   Exit For
                End If
             Next LoopCounter
             If Worksheets("Cover Page").Cells(BSegLoopCounter + 1, 12).Value = "" Then
                   Exit For
             End If
          Next BSegLoopCounter
       Case "2" 'Feb
       Case "3" 'Mar
       Case "4" 'Apr
       Case "5" 'May
       Case "6" 'Jun
       Case "7" 'Jul
       Case "8" 'Aug
       Case "9" 'Sep
       Case "10" 'Oct
       Case "11" 'Nov
       Case "12" 'Dec
    End Select
'   Format to Currency, 0 Decimals, with $ sign
    Worksheets("Dollar Sales (New)").Activate
    Worksheets("Dollar Sales (New)").Range("E8:G9").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("L8:N9").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("S8:U9").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("Z8:AB9").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("E12:G24").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("L12:N24").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("S12:U24").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("Z12:AB24").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("E29:G38").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("L29:N38").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("S29:U38").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("Z29:AB38").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("E43:G52").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("L43:N52").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("S43:U52").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("Z43:AB52").Select
    Selection.NumberFormat = "$#,##0_);($#,##0)"
    Worksheets("Dollar Sales (New)").Range("A1").Select
    Worksheets("Cover Page").Activate
End Sub
 
Upvote 0
I tried to make a Module out of one of the For/Next loops and I get an error:

Run-time error '1004':
Application-defined or object-defined error

As soons as it hits the first line in the Sub I get the error.

I copied out the following:
Code: 
Sub Get12MonthsData()
For AA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA).Value  'CY Annual Budget
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 49).Value 'LY Annual Sales
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, AA - 97).Value '2LY Annual Sales
Next AA
End Sub
 
Upvote 0
elliott740,

I have defined your variable AA, ans lngAA. AA is also a column name.

Try:

Code: 
Sub Get12MonthsData()
Dim lngAA As Long
For lngAAAA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA).Value  'CY Annual Budget
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA - 49).Value 'LY Annual Sales
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA - 97).Value '2LY Annual Sales
Next AA
End Sub


Have a great day,
Stan
 
Upvote 0
elliott740,

And, you may want to shorten up your code (the beginning of your macro):

Code: 
    With Worksheets("Dollar Sales (New)")
        .Range("E8:G9").Clear
        .Range("L8:N9").Clear
        .Range("S8:U9").Clear
        .Range("Z8:AB9").Clear
        .Range("E12:G24").Clear
        .Range("L12:N24").Clear
        .Range("S12:U24").Clear
        .Range("Z12:AB24").Clear
        .Range("E29:G38").Clear
        .Range("L29:N38").Clear
        .Range("S29:U38").Clear
        .Range("Z29:AB38").Clear
        .Range("E43:G52").Clear
        .Range("L43:N52").Clear
        .Range("S43:U52").Clear
        .Range("Z43:AB52").Clear
    End With


Have a great day,
Stan
 
Upvote 0
I just changed the code (replaced AA with ColumnCtr) to the following (see below) and I am still getting the same error. The debugger is showing it as the first "Worksheets" line.

Thanks,

Tom

Code: 
Sub Get12MonthsData()
   For ColumnCtr = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
      Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, ColumnCtr).Value  'CY Annual Budget
      Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, ColumnCtr - 49).Value 'LY Annual Sales
      Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, ColumnCtr - 97).Value '2LY Annual Sales
   Next ColumnCtr
End Sub
 
Upvote 0
elliott740,

And, at the end of your macro:

Code: 
    With Worksheets("Dollar Sales (New)")
        .Range("E8:G9").NumberFormat = "$#,##0_);($#,##0)"
        .Range("L8:N9").NumberFormat = "$#,##0_);($#,##0)"
        .Range("S8:U9").NumberFormat = "$#,##0_);($#,##0)"
        .Range("Z8:AB9").NumberFormat = "$#,##0_);($#,##0)"
        .Range("E12:G24").NumberFormat = "$#,##0_);($#,##0)"
        .Range("L12:N24").NumberFormat = "$#,##0_);($#,##0)"
        .Range("S12:U24").NumberFormat = "$#,##0_);($#,##0)"
        .Range("Z12:AB24").NumberFormat = "$#,##0_);($#,##0)"
        .Range("E29:G38").NumberFormat = "$#,##0_);($#,##0)"
        .Range("L29:N38").NumberFormat = "$#,##0_);($#,##0)"
        .Range("S29:U38").NumberFormat = "$#,##0_);($#,##0)"
        .Range("Z29:AB38").NumberFormat = "$#,##0_);($#,##0)"
        .Range("E43:G52").NumberFormat = "$#,##0_);($#,##0)"
        .Range("L43:N52").NumberFormat = "$#,##0_);($#,##0)"
        .Range("S43:U52").NumberFormat = "$#,##0_);($#,##0)"
        .Range("Z43:AB52").NumberFormat = "$#,##0_);($#,##0)"
    End With
    Worksheets("Dollar Sales (New)").Range("A1").Select
    Worksheets("Cover Page").Activate


Have a great day,
Stan
 
Upvote 0
elliott740,

I have defined your variable AA, ans lngAA. AA is also a column name.

Try:


Code: 
Dim lngAA As Long
For lngAA = 103 To 147 Step 4 'Loop to Capture 12 Months (Annual Data)
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 26).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA).Value  'CY Annual Budget
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 27).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA - 49).Value 'LY Annual Sales
   Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value = Worksheets("Dollar Sales (New)").Cells(RowCounter, 28).Value + Worksheets("BW Rpt - Summary").Cells(LoopCounter, lngAA - 97).Value '2LY Annual Sales
Next lngAA



Have a great day,
Stan
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
865
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
462
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
342
VBAEXCELNew
V
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
365
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,216,106
Messages
6,128,863
Members
449,473
Latest member
soumyahalder4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top