VBA same sheet cut/paste request

crook_101

crook_101

Well-known Member
Joined
Oct 20, 2008
Messages
687
Hi again,

I have a thread started here http://www.mrexcel.com/forum/showthread.php?t=553996 but i think the complexity of it is putting a lot of people off:( Anyway to simplify the task I have amended my request and have a workaround for a lot of the complex mail merge. Boring, sorry, to the facts:

Sheet layout as below:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Tech No</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Issued</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Cancelled</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Incomplete</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Unfulfilled</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Completed</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Revisits</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Right First Time</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">RFT Score</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Completion Rate</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Revisit Rate</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Job Reference</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Visit Date</TD><TD style="BORDER-LEFT: black 1px solid">Job Type</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">107378680</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">02/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">107459192</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">07/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">107500901</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">19</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1522</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107648350</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">23/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">20</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1522</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107648639</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">24/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">26</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107433686</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">27</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107656430</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">24/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">28</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107648056</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">29</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107507996</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">31/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">30</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107740844</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">28/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">31</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107775786</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">31/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD></TR></TBODY></TABLE>
Detail​

I need some VBA to to pick up the ID in column A (tech no) and copy each row relating to (example) 198 onto the first row where there is a refernce to 198. Loop through all of the tech no identitiers in column A and cut/paste the same as above. There is no relationship between the tech number and the number of individual records so I can't use relative cell references that is why I believe that I need to have it coded. The result would look like the below:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH><TH>AH</TH><TH>AI</TH><TH>AJ</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH><TH>AN</TH><TH>AO</TH><TH>AP</TH><TH>AQ</TH><TH>AR</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Tech No</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Issued</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Cancelled</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Incomplete</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Unfulfilled</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Completed</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Revisits</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Right First Time</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">RFT Score</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Completion Rate</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Revisit Rate</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Job Reference</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Visit Date</TD><TD style="BORDER-LEFT: black 1px solid">Job Type</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">107378680</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">02/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1.07E+08</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">07/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">198</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1.08E+08</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1522</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107648350</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">23/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1522</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1.08E+08</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">24/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">23</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">107433686</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1.08E+08</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">24/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Install</TD><TD style="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">2121</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">0.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">100.00%</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1.08E+08</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21/03/2011</TD><TD style="BORDER-LEFT: black 1px solid">Service</TD></TR></TBODY></TABLE>
Detail​


Any advice, guidance, code would be fanastic as I am at a loss with this one.

Many thanks,
Ian
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
1) Is the list already sorted?
2) It appears that A3:N3 was copied to P2:AC2 and A4:N4 was copied to AE2:AR2. Is that correct?
3) Should the data in the second and subsequent rows with duplicate tech Numbers be a) deleted, b) erased, c) left alone?
4) Should the data in the original worksheet be left alone and a new worksheet created in the "wide" format? (recommended)
5) Could you ever have more than 1092 duplicate Tech numbers (if so, this will exceed column XFD - 16384 columns)

It seems as if you may end up with a worksheet that is very wide. You will have to scroll to the right for each row to see if there is any more data. Do you want to insert a column on the left that indicates how many data sets of 15 columns (14 with data + 1 blank column) exist on that row?
 
Upvote 0
Hi Phil,

Thanks for coming back to me on this one - i did get sorted and will post code below from Mark....For info - unusual sheet as it is part of a huge mail merge my team do every quarter into word, print and envelop - not that bad as they have an auto-enveloper but the data is split into several merges or Word falls over with the ~600K rows from the master file!!!

Thanks again for coming back to me on this, much appreciated:)

Code: 
Sub NamesData_v7()
'
'Prg    : NamesData_v7
'Author : Markmzz
'Date   : 01/06/2011
'Version: 07
'
    'Sets the macro variables explicitly
    Dim LastRowL1, LastRowL2, LastColL1, NextCol As Long
    Dim RL1, RL2, CL1, CL2, NCL1, NCL2 As Long
    Dim myArrayL1, myArrayNL2, myArrayCL2 As Variant
    
    'Disable screen updating
    'Application.ScreenUpdating = False
    
    'Determines the total number of rows in the first list
    LastRowL1 = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Determines the total number of columns in the first list
    LastColL1 = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'Stores the total number of columns in the first
    'list in variable NCL1
    NCL1 = LastColL1
    
    'Sort, in ascending order, the first list
    Range(Cells(2, 1), Cells(LastRowL1, LastColL1)).Sort _
        Key1:=Range("A1"), _
        Order1:=xlAscending
        
    'Sets the dimensions of the array corresponds
    'to the data from the first list
    ReDim myArrayL1(1 To LastRowL1, 1 To LastColL1)
    
    'Stores the data from the first list in the corresponding array
    myArrayL1 = Range(Cells(1, 1), Cells(LastRowL1, LastColL1))
    
    'Defines the starting column of the second list
    '(two columns to the right of first list)
    NextCol = LastColL1 + 2
    
    'Creates the second list starting column,a column without
    'repeating the data of the first column of the first list
    Range(Cells(1, 1), Cells(LastRowL1, 1)).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Range(Cells(1, NextCol).Address), _
        Unique:=True
    Cells(1, NextCol).Font.Bold = False
    
    'Determines the number of rows in the second list
    LastRowL2 = Cells(Rows.Count, NextCol).End(xlUp).Row
    
    'Defines the dimensions of the array corresponds to the
    'data in the first column of the second list
    ReDim myArrayL2(1 To LastRowL2, 1)
    
    'Stores the data in the first column of the second list in
    'the corresponding array
    myArrayL2 = Range(Cells(1, NextCol), Cells(LastRowL2, NextCol))
    
    'Determines the maximum number of repetitions of a single
    'data from the first column of the first list
    Cells(1, NextCol + 1).FormulaArray = "=Large(Countif(R2C1:R" & LastRowL1 & _
            "C1,R2C" & NextCol & ":R" & LastRowL2 & "C" & NextCol & "),1)"
    
    'Determines the number total of columns in the second list
    NCL2 = Cells(1, NextCol + 1).Value * NCL1
    'Defines the dimensions of the array corresponds to the data from the
    'second list
    ReDim myArrayCL2(1 To LastRowL2, 1 To NCL2)
  
    'Defines two and one as the current row and column numbers of the first list
    RL1 = 2
    CL1 = 1
    
    'Navigates the data in the field of the first column of the second list
    For RL2 = 2 To LastRowL2
        'Show the progress of the macro in the status bar
        Application.StatusBar = "Processing row " & RL2 & " of " & LastRowL2
        
        'Defines one with the current column number of the second list
        CL2 = 1
        
        'Navigates by data field of the first column of the first list that
        'are equal to the current field of the first column of the second list
        Do While myArrayL1(RL1, 1) = myArrayL2(RL2, 1)
            For CL1 = 1 To LastColL1
                'Fills the array to the second list
                myArrayCL2(RL2, CL2) = myArrayL1(RL1, CL1)
                CL2 = CL2 + 1
            Next CL1
            
            RL1 = RL1 + 1
            'Checks if the current line number of the first list is greater
            'than the total number of rows in the same list
            If RL1 > LastRowL1 Then
                Exit Do
                'Exits the loop
            End If
        Loop
    Next RL2
    
    'Populates the column labels from the second list
    For CL2 = 1 To NCL2
            CL1 = CL2 - Int((CL2 - 1) / (LastColL1)) * (LastColL1)
            myArrayCL2(1, CL2) = myArrayL1(1, CL1)
    Next CL2
    Range(Cells(1, NextCol), Cells(LastRowL2, NextCol + NCL2 - 1)) = myArrayCL2
    
    'Autofit the columns of 2nd List
    Cells(1, NextCol).CurrentRegion.EntireColumn.AutoFit
        
    'Enable screen updating
    'Application.ScreenUpdating = True
    
    'Reset the Status Bar of the Excel
    Application.StatusBar = False
End Sub

Ian
 
Upvote 0
Crook_101,

Hi again, try this:

Sheet Detail Before

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Tech No</TD><TD>Issued</TD><TD>Cancelled</TD><TD>Incomplete</TD><TD>Unfulfilled</TD><TD>Completed</TD><TD>Revisits</TD><TD>Right First Time</TD><TD>RFT Score</TD><TD>Completion Rate</TD><TD>Revisit Rate</TD><TD>Job Reference</TD><TD>Visit Date</TD><TD>Job Type</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107378680</TD><TD style="TEXT-ALIGN: right">02/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">107459192</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>Service</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107500901</TD><TD style="TEXT-ALIGN: right">16/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b0f0">1522</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107648350</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b0f0">1522</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107648639</TD><TD style="TEXT-ALIGN: right">24/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107433686</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107656430</TD><TD style="TEXT-ALIGN: right">24/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">107648056</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>Service</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107507996</TD><TD style="TEXT-ALIGN: right">31/03/2011</TD><TD>Install</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">107740844</TD><TD style="TEXT-ALIGN: right">28/03/2011</TD><TD>Service</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107775786</TD><TD style="TEXT-ALIGN: right">31/03/2011</TD><TD>Service</TD></TR></TBODY></TABLE>



Sheet Detail After



<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH><TH>Z</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH><TH>AH</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Tech No</TD><TD>Issued</TD><TD>Cancelled</TD><TD>Incomplete</TD><TD>Unfulfilled</TD><TD>Completed</TD><TD>Revisits</TD><TD>Right First Time</TD><TD>RFT Score</TD><TD>Completion Rate</TD><TD>Revisit Rate</TD><TD>Job Reference</TD><TD>Visit Date</TD><TD>Job Type</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107378680</TD><TD style="TEXT-ALIGN: right">02/03/2011</TD><TD>Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">100,00%</TD><TD style="TEXT-ALIGN: right">107459192</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>Service</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">198</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b0f0">1522</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107648350</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00b0f0">1522</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107648639</TD><TD style="TEXT-ALIGN: right">24/03/2011</TD><TD>Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107433686</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right">0,00%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">107656430</TD><TD style="TEXT-ALIGN: right">24/03/2011</TD><TD>Install</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2121</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Detail​




Code in VBA

Code: 
[COLOR=blue]Sub NewDetail()[/COLOR]
[COLOR=blue]'[/COLOR]
[COLOR=blue]'Prg    : NewDetail[/COLOR]
[COLOR=blue]'Author : Markmzz[/COLOR]
[COLOR=blue]'Date   : 03/06/2011[/COLOR]
[COLOR=blue]'Version: 01[/COLOR]
[COLOR=blue]'[/COLOR]
[COLOR=blue]  'Sets the macro variables explicitly[/COLOR]
[COLOR=blue]  Dim LastRowL1, LastRowRL, LastColL1, NextCol As Long[/COLOR]
[COLOR=blue]  Dim RL2, CL2, NRL1, NCL1 As Long[/COLOR]
[COLOR=blue]  Dim RRL, NRRL, NRFL1, NRF As Long[/COLOR]
[COLOR=blue]  Dim myArrayRL As Variant[/COLOR]
[COLOR=blue]  Dim ws1 As Worksheet[/COLOR]
[COLOR=blue]  Dim myFilterL1 As Range[/COLOR]
 
[COLOR=blue]  'Disable screen updating[/COLOR]
[COLOR=blue]  Application.ScreenUpdating = False[/COLOR]
 
[COLOR=blue]  'Store the reference of the sheet Detail[/COLOR]
[COLOR=blue]  Set ws1 = Sheets("Detail")[/COLOR]
 
[COLOR=blue]  'Verify if the macro has already been run on the sheet Detail[/COLOR]
[COLOR=blue]  If WorksheetFunction.Count(ws1.Columns(15)) > 0 Or _[/COLOR]
[COLOR=blue]          WorksheetFunction.Count(ws1.Columns(16)) > 0 Then[/COLOR]
[COLOR=blue]      MsgBox "The macro has already been run on the sheet Detail"[/COLOR]
[COLOR=blue]      Exit Sub[/COLOR]
[COLOR=blue]  End If[/COLOR]
 
[COLOR=blue]  'Determines the last Row in the First list[/COLOR]
[COLOR=blue]  'Start in the column A (1)[/COLOR]
[COLOR=blue]  LastRowL1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row[/COLOR]
 
[COLOR=blue]  'Determines the last Column in the First list[/COLOR]
[COLOR=blue]  'Start in the row 1[/COLOR]
[COLOR=blue]  LastColL1 = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column[/COLOR]
 
[COLOR=blue]  'Stores the total number of Columns in the First[/COLOR]
[COLOR=blue]  'list in variable NCL1[/COLOR]
[COLOR=blue]  NCL1 = LastColL1[/COLOR]
 
[COLOR=blue]  'Sort, in ascending order, the First list[/COLOR]
[COLOR=blue]  Range(ws1.Cells(2, 1), ws1.Cells(LastRowL1, LastColL1)).Sort _[/COLOR]
[COLOR=blue]      Key1:=ws1.Range("A1"), _[/COLOR]
[COLOR=blue]      Order1:=xlAscending[/COLOR]
 
[COLOR=blue]  'Determines the last Row in the First list After Sort[/COLOR]
[COLOR=blue]  'Start in the column A (1)[/COLOR]
[COLOR=blue]  LastRowL1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row[/COLOR]
 
[COLOR=blue]  'Stores the total number of Rows in the First[/COLOR]
[COLOR=blue]  'list in variable NRL1[/COLOR]
[COLOR=blue]  NRL1 = LastRowL1[/COLOR]
 
[COLOR=blue]  'Defines the starting column of the second list[/COLOR]
[COLOR=blue]  '(two columns to the right of first list)[/COLOR]
[COLOR=blue]  NextCol = LastColL1 + 2[/COLOR]
 
[COLOR=blue]  'Creates the Reference list[/COLOR]
[COLOR=blue]  Range(ws1.Cells(1, 1), ws1.Cells(LastRowL1, 1)).AdvancedFilter _[/COLOR]
[COLOR=blue]      Action:=xlFilterCopy, _[/COLOR]
[COLOR=blue]      CopyToRange:=ws1.Range(ws1.Cells(1, NextCol - 1).Address), _[/COLOR]
[COLOR=blue]      Unique:=True[/COLOR]
 
[COLOR=blue]  'Determines the last Row in the Reference list[/COLOR]
[COLOR=blue]  'Start in the column O (15)[/COLOR]
[COLOR=blue]  LastRowRL = ws1.Cells(ws1.Rows.Count, NextCol - 1).End(xlUp).Row[/COLOR]
[COLOR=blue]  'Stores the total number of data Rows in the Reference[/COLOR]
[COLOR=blue]  'list in the variable NRRL[/COLOR]
[COLOR=blue]  NRRL = LastRowRL - 1[/COLOR]
 
[COLOR=blue]  'Defines the dimensions of the array corresponds to the[/COLOR]
[COLOR=blue]  'data in the Reference list[/COLOR]
[COLOR=blue]  ReDim myArrayRL(1 To NRRL, 1)[/COLOR]
 
[COLOR=blue]  'Stores the data of the Reference list in the corresponding array[/COLOR]
[COLOR=blue]  'Start in the row 2 and column A (1)[/COLOR]
[COLOR=blue]  myArrayRL = ws1.Range(ws1.Cells(2, NextCol - 1), ws1.Cells(LastRowRL, NextCol - 1))[/COLOR]
 
[COLOR=blue]  'Start the Current Row of the Second list in 2[/COLOR]
[COLOR=blue]  RL2 = 2[/COLOR]
 
[COLOR=blue]  'Navigates by records of the Reference list[/COLOR]
[COLOR=blue]  For RRL = 1 To NRRL[/COLOR]
[COLOR=blue]      'Show the progress of the macro in the status bar[/COLOR]
[COLOR=blue]      Application.StatusBar = "Processing Row " & RRL & " of " & NRRL & _[/COLOR]
[COLOR=blue]          " of the Reference list"[/COLOR]
 
[COLOR=blue]      'Filters the data in the First list corresponding to the current[/COLOR]
[COLOR=blue]      'record of Reference list[/COLOR]
[COLOR=blue]      ws1.Range(ws1.Cells(1, 1), ws1.Cells(LastRowL1, LastColL1)).AutoFilter _[/COLOR]
[COLOR=blue]          Field:=1, _[/COLOR]
[COLOR=blue]          Criteria1:=myArrayRL(RRL, 1)[/COLOR]
 
[COLOR=blue]      'Stores the reference of the filtered records in the variable myFilterL1[/COLOR]
[COLOR=blue]      Set myFilterL1 = ws1.AutoFilter.Range.SpecialCells(xlCellTypeVisible)[/COLOR]
 
[COLOR=blue]      'Determines the number of the Rows of the data filtered[/COLOR]
[COLOR=blue]      NRFL1 = myFilterL1.Count / myFilterL1.Columns.Count[/COLOR]
[COLOR=blue]      ws1.Cells(1, 1).AutoFilter[/COLOR]
[COLOR=blue]      CL2 = NextCol[/COLOR]
[COLOR=blue]      NRF = 2[/COLOR]
 
[COLOR=blue]      'If exist data filtered[/COLOR]
[COLOR=blue]      If NRFL1 > 1 Then[/COLOR]
[COLOR=blue]          'Copy the data filtered to the column NexCol (Two columns to the[/COLOR]
[COLOR=blue]          'right of the first[/COLOR]
[COLOR=blue]          myFilterL1.Copy Destination:=ws1.Cells(RL2 - IIf(RRL = 1, 1, 0), CL2)[/COLOR]
 
[COLOR=blue]          'Stores the reference of the copy records in the variable myFilterL1[/COLOR]
[COLOR=blue]          Set myFilterL1 = ws1.Cells(RL2 - IIf(RRL = 1, 1, 0), CL2).Resize(NRFL1, NCL1)[/COLOR]
[COLOR=blue]          NRF = NRF + 1[/COLOR]
[COLOR=blue]          CL2 = CL2 + NCL1 + 1[/COLOR]
 
[COLOR=blue]          Do While NRF <= NRFL1[/COLOR]
[COLOR=blue]              'Move the 2nd and others rows to the right place[/COLOR]
[COLOR=blue]              myFilterL1.Rows(NRF).Cut Destination:=ws1.Cells(RL2, CL2)[/COLOR]
[COLOR=blue]              CL2 = CL2 + NCL1 + 1[/COLOR]
[COLOR=blue]              NRF = NRF + 1[/COLOR]
[COLOR=blue]          Loop[/COLOR]
[COLOR=blue]      End If[/COLOR]
[COLOR=blue]      If RRL <> 1 Then[/COLOR]
[COLOR=blue]          'Erase columns labels the 2nd and others records of the[/COLOR]
[COLOR=blue]          'Reference list[/COLOR]
[COLOR=blue]          myFilterL1.Resize(1).Delete xlShiftUp[/COLOR]
[COLOR=blue]      End If[/COLOR]
[COLOR=blue]      RL2 = RL2 + 1[/COLOR]
[COLOR=blue]  Next RRL[/COLOR]
 
[COLOR=blue]  'Delete de columns of the First list and[/COLOR]
[COLOR=blue]  'Reference list[/COLOR]
[COLOR=blue]  ws1.Cells(1, 1).Resize(1, LastColL1 + 1).EntireColumn.Delete[/COLOR]
 
[COLOR=blue]  'Autofit the columns[/COLOR]
[COLOR=blue]  ws1.Cells(1, 1).Select[/COLOR]
[COLOR=blue]  ws1.Rows(1).EntireColumn.AutoFit[/COLOR]
 
[COLOR=blue]  'Enable screen updating[/COLOR]
[COLOR=blue]  Application.ScreenUpdating = True[/COLOR]
 
[COLOR=blue]  'Reset the Status Bar of the Excel[/COLOR]
[COLOR=blue]  Application.StatusBar = False[/COLOR]
 
[COLOR=blue]  'Frees the memory used by references variables[/COLOR]
[COLOR=blue]  'from the sheets and the ranges[/COLOR]
[COLOR=blue]  Set myFilterL1 = Nothing[/COLOR]
[COLOR=blue]  Set ws1 = Nothing[/COLOR]
 
[COLOR=blue]End Sub[/COLOR]

This solve your problem?

Markmzz
 
Upvote 0
Crook_101,

I test the macro NewDetail with 50,000 records and 4,515 records without repeating field Tech In and it took a long time in my computer. Nothing good.

So, I created a second macro CopyDetail (much faster). In the new test that I did with this new macro, it processe the same set of records in 50 sec.

Then, as a faster alternative, test the macro CopyDetail below:

Note: I put several comments in my macro to facilitate understanding. Soon, it seems to be long, but it is not.

Code: 
Sub CopyDetail()
'
'Prg    : CopyDetail
'Author : Markmzz
'Date   : 04/06/2011
'Version: 01
'
    'Defines the macro variables explicitly
    Dim LastRowL1, LastColL1, TotalRRL As Long
    Dim CRRL, CRL2, CGCL2, mySGCL2 As Long
    Dim myRL, myCell As Range
    Dim myPCell As Variant
 
    'Disable screen updating
    Application.ScreenUpdating = False
 
    'Verify if the macro has already been run on the active sheet
    If WorksheetFunction.Count(Columns("O:P")) > 0 Then
        MsgBox "The macro has already been run on the active sheet"
        Exit Sub
    End If
 
    'Determines the total number of Rows in the First list
    LastRowL1 = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Determines the total number of Columns in the First list
    LastColL1 = Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Sort, in ascending order, the First list
    Range(Cells(2, 1), Cells(LastRowL1, LastColL1)).Sort _
        Key1:=Range("A1"), _
        Order1:=xlAscending
 
    'Store the reference to the data os the first column
    'in the First list in the Reference variable myRL
    Set myRL = Range(Cells(2, 1), Cells(LastRowL1, 1))
 
    'Initialize the variables
    'Previous cell in the reference list
    myPCell = ""
    'The current row in the 2nd list
    CRL2 = 2
    'The columns group count of the 2nd list
    CGCL2 = 2
    'The current row in the Reference list
    CRRL = 1
 
    'Determines the total os Rows in the Reference list
    TotalRRL = myRL.Rows.Count
 
    'Copy the labels of the First list to first group
    'of the Second list
    Cells(1, 1).Resize(1, LastColL1).Copy _
        Destination:=Cells(1, LastColL1 + 2)
 
    'Navigates for the Reference list
    For Each myCell In myRL
        'Show the progress of the macro in the status bar
        Application.StatusBar = "Processing Row " & CRRL _
            & " of " & TotalRRL & " of the Reference list"
 
        'Verify if the Record current in the Reference list
        'is equal to the previous Record in the same list
        If myCell.Value = myPCell Then
            'Copy the data corresponding the current Record
            'of the Reference list to the second group and others
            'of the Second list
            mySGCL2 = (LastColL1 + 2) * CGCL2 - (CGCL2 - 1)
            myCell.Resize(1, LastColL1).Copy _
                Destination:=Cells(CRL2 - 1, mySGCL2)
            CGCL2 = CGCL2 + 1
        Else
            'Copy the data corresponding the current Record
            'of the Reference list to the first group of
            'the Second list
            myCell.Resize(1, LastColL1).Copy _
                Destination:=Cells(CRL2, LastColL1 + 2)
            CRL2 = CRL2 + 1
            myPCell = myCell
            CGCL2 = 2
        End If
        CRRL = CRRL + 1
    Next myCell
    'Show the progress of the macro in the status bar
    Application.StatusBar = "Formating the Second list"
 
    'Delete de columns of the First list and of the
    'blank column
    Cells(1, 1).Resize(1, LastColL1 + 1).EntireColumn.Delete
 
    'Autofit the columns of the active sheet
    Cells(1, 1).Select
    Rows(1).EntireColumn.AutoFit
 
    'Enable screen updating
    Application.ScreenUpdating = True
 
    'Reset the Status Bar of the Excel
    Application.StatusBar = False
 
    'Frees the memory used by reference variable myRL
    'Range myRL
    Set myRL = Nothing
End Sub

Markmzz
 
Upvote 0
Hi Mark,

Cheers for this - i will have a play on Monday with this when I am back at work - thanks for the email outwith this thread, much appreciated.

Get back to you on Monday - when I have my work laptop back and not ipad/blackberry combo as both of these are not very good for excel!!:eeek:

Cheers,
Ian
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
209
Logit
Logit
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

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