Macro to Ins/Autofill 3 rows with 3 row source

wwiebe

New Member
Joined
Jun 20, 2008
Messages
8
First post!
So I've seen a few posts that get close to this issue, but my grasp of VB isn't nearly good enough yet to modify code to make the solutions work for my situation. I have a chart with each "row" consisting of 3 distinct, sequential actual rows. I'd like a macro to add a "chart row" to accommodate more entries as required, copying down the formulas just the the way AutoFill would do by selecting the three rows together and dragging down, which I do now.
I don't know whether I'm posting the HTML properly, but here we go:
<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="15" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Mod V8TEST.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="15" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); 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 align="right" valign="center"><form name="formCb658016"><input onclick='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);' value="Copy Formula" name="btCb074442" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="15" bgcolor="white"><table border="0"><tbody><tr><form name="formFb431795"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name="sltNb305190"><option value="=A5" selected="selected">C5</option><option value="=IF(E5="","",E5/10.7641)">D5</option><option value="=IF(G5="","",G5/10.7641)">F5</option><option value="=IF(I5="","",I5/10.7641)">H5</option><option value="=CONCATENATE(B5,"#")">B6</option><option value="=CONCATENATE(B5,"@")">B7</option><option value="=A8">C8</option><option value="=IF(E8="","",E8/10.7641)">D8</option><option value="=IF(G8="","",G8/10.7641)">F8</option><option value="=IF(I8="","",I8/10.7641)">H8</option><option value="=CONCATENATE(B8,"#")">B9</option><option value="=CONCATENATE(B8,"@")">B10</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=A5" name="txbFb428726"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>B</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>C</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>D</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>F</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>H</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>I</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>J</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>K</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>L</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>M</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>N</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>O</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>3</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 1.5pt 0.5pt 0.5pt; font-weight: bold; font-size: 9pt; vertical-align: middle; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;">Room Name</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 9pt; vertical-align: bottom; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;" rowspan="2">Floor Area (m²)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;" rowspan="2"> Floor Area (SF)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;" rowspan="2">View Area (m²)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;" rowspan="2">View Area (SF)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;" rowspan="2">Perimeter Floor Area (m²)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;" rowspan="2"> Perimeter Floor Area (SF)</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;">Number</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;"> Type</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;" rowspan="2"> Regularly Occupied, Gen.</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;" rowspan="2">Individual Multi-Occupant</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(150, 150, 150); text-align: center;" rowspan="2"> Group Multi-Occupant</td><td style="border-top: 1.5pt solid rgb(0, 0, 0); border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;" rowspan="2"> Private Office</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 7pt; vertical-align: bottom; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(128, 128, 128); text-align: center;"> No Identical Names</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 9pt; vertical-align: bottom; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 9pt; vertical-align: bottom; color: rgb(192, 192, 192); font-family: Arial; background-color: rgb(51, 51, 51); text-align: center;" colspan="2">W i n d o w s</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room1</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;" rowspan="3">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">92.9</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3">1000</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">
</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">
</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">1.0</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">W1</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3">x</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3"> </td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3"> </td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3">x</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 8pt; vertical-align: middle; color: rgb(192, 192, 192); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room1#</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">3.0</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">W2</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: middle; color: rgb(192, 192, 192); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room1@</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">2.0</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">W5</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room2</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;" rowspan="3">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">696.8</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3">7500</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">
</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 204, 153); text-align: center;" rowspan="3">
</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(204, 255, 204); text-align: center;" rowspan="3"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">2.0</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">W1</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3">x</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3"> </td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3"> </td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;" rowspan="3"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 8pt; vertical-align: middle; color: rgb(192, 192, 192); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room2#</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">1.0</td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;">W3</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>10</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 8pt; vertical-align: middle; color: rgb(192, 192, 192); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Room2@</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 1.5pt solid rgb(0, 0, 0); border-bottom: 1.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>11</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 8pt; vertical-align: middle; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>12</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 8pt; vertical-align: middle; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>13</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 8pt; vertical-align: middle; color: rgb(255, 255, 255); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(255, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-weight: bold; font-size: 14pt; vertical-align: middle; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: center;"> </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="15"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Room & Window Schedule</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>
As a related question, I came across the following code (a similar version of which is posted elsewhere on this forum) which has served well to add multiple additional rows from a single source row for another matrix I have in the same spreadsheet. BUT I'd like it to automatically select the last row of that chart, rather than having to select a cell in that row to identify it. Any advice?
[FONT=&quot]Sub InsertRowsAndFillFormulas_caller()
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <markhill@charm.net.nospam>
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub</markhill@charm.net.nospam>[/FONT]
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

wwiebe

New Member
Joined
Jun 20, 2008
Messages
8
I've found this code, posted by Mick G, in further searches on this subject:
Code:
Dim rng As range
Set rng = CommandButton2.TopLeftCell
rng.EntireRow.Insert
rng.Offset(-1).Formula = rng.Offset(-2).Formula

This code is for inserting a row and copying formulas relative to a button's position. Perfect! I just need it to insert THREE rows with their respective formulas and formatting. Is there any way to adapt this?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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
Top