Macro to copy & paste range

[Alex O]

Can anyone help with a macro that will copy K&I and paste the values into AH so that it's formatted as VSB/09-2375.40.<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 77px"><COL style="WIDTH: 153px"><COL style="WIDTH: 152px"><COL style="WIDTH: 68px"><COL style="WIDTH: 67px"><COL style="WIDTH: 40px"><COL style="WIDTH: 74px"><COL style="WIDTH: 77px"><COL style="WIDTH: 82px"><COL style="WIDTH: 63px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">120499</TD><TD style="TEXT-ALIGN: left">A-1 Asphalt Driveways</TD><TD style="TEXT-ALIGN: left">9233 Bayberry Ave</TD><TD> </TD><TD style="TEXT-ALIGN: left">Manassas</TD><TD style="TEXT-ALIGN: left">VA</TD><TD style="TEXT-ALIGN: left">20110</TD><TD style="TEXT-ALIGN: left">7033854154</TD><TD style="TEXT-ALIGN: left">2,375.40</TD><TD style="TEXT-ALIGN: left">01/10/11</TD><TD style="TEXT-ALIGN: left">VSB/09</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">202609</TD><TD style="TEXT-ALIGN: left">Knotts Interiors Inc</TD><TD style="TEXT-ALIGN: left">1505 N Hermitage Rd</TD><TD> </TD><TD style="TEXT-ALIGN: left">Hermitage</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">16148-3104</TD><TD style="TEXT-ALIGN: left">7249625766</TD><TD style="TEXT-ALIGN: left">1,356.25</TD><TD style="TEXT-ALIGN: left">12/16/10</TD><TD style="TEXT-ALIGN: left">LMW/09</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">202609</TD><TD style="TEXT-ALIGN: left">Knotts Interiors Inc</TD><TD style="TEXT-ALIGN: left">1505 N Hermitage Rd</TD><TD> </TD><TD style="TEXT-ALIGN: left">Hermitage</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">16148-3104</TD><TD style="TEXT-ALIGN: left">7249625766</TD><TD style="TEXT-ALIGN: left">1,356.25</TD><TD style="TEXT-ALIGN: left">12/16/10</TD><TD style="TEXT-ALIGN: left">LMW/10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">203896</TD><TD style="TEXT-ALIGN: left">David Tours & Travel</TD><TD style="TEXT-ALIGN: left">14005 McNaulty Rd</TD><TD> </TD><TD style="TEXT-ALIGN: left">Philadelphia</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">19154</TD><TD style="TEXT-ALIGN: left">8888442540</TD><TD style="TEXT-ALIGN: left">805.50</TD><TD style="TEXT-ALIGN: left">10/07/10</TD><TD style="TEXT-ALIGN: left">LMK/10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">203896</TD><TD style="TEXT-ALIGN: left">David Tours & Travel</TD><TD style="TEXT-ALIGN: left">14005 McNaulty Rd</TD><TD> </TD><TD style="TEXT-ALIGN: left">Philadelphia</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">19154</TD><TD style="TEXT-ALIGN: left">8888442540</TD><TD style="TEXT-ALIGN: left">805.50</TD><TD style="TEXT-ALIGN: left">10/07/10</TD><TD style="TEXT-ALIGN: left">MSF/11</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">229479</TD><TD style="TEXT-ALIGN: left">K & A Auto Salvage Inc</TD><TD style="TEXT-ALIGN: left">2160-66 E Somerset St</TD><TD> </TD><TD style="TEXT-ALIGN: left">Philadelphia</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">19134-3926</TD><TD style="TEXT-ALIGN: left">2154234255</TD><TD style="TEXT-ALIGN: left">2,351.50</TD><TD style="TEXT-ALIGN: left">08/31/09</TD><TD style="TEXT-ALIGN: left">PHM/10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">233149</TD><TD style="TEXT-ALIGN: left">Champion Granite</TD><TD style="TEXT-ALIGN: left">1426 Millett St</TD><TD> </TD><TD style="TEXT-ALIGN: left">Dunmore</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">18512-2626</TD><TD style="TEXT-ALIGN: left">5708404176</TD><TD style="TEXT-ALIGN: left">1,548.00</TD><TD style="TEXT-ALIGN: left">08/10/10</TD><TD style="TEXT-ALIGN: left">SCM/11</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">248311</TD><TD style="TEXT-ALIGN: left">Evergreen Trailer Sales Inc</TD><TD style="TEXT-ALIGN: left">569 S Erie St</TD><TD> </TD><TD style="TEXT-ALIGN: left">Mercer</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">16137-4101</TD><TD style="TEXT-ALIGN: left">7246623570</TD><TD style="TEXT-ALIGN: left">9,268.71</TD><TD style="TEXT-ALIGN: left">02/28/11</TD><TD style="TEXT-ALIGN: left">PIM/10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">248311</TD><TD style="TEXT-ALIGN: left">Evergreen Trailer Sales Inc</TD><TD style="TEXT-ALIGN: left">569 S Erie St</TD><TD> </TD><TD style="TEXT-ALIGN: left">Mercer</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">16137-4101</TD><TD style="TEXT-ALIGN: left">7246623570</TD><TD style="TEXT-ALIGN: left">9,268.71</TD><TD style="TEXT-ALIGN: left">02/28/11</TD><TD style="TEXT-ALIGN: left">BNP/10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">248311</TD><TD style="TEXT-ALIGN: left">Evergreen Trailer Sales Inc</TD><TD style="TEXT-ALIGN: left">569 S Erie St</TD><TD> </TD><TD style="TEXT-ALIGN: left">Mercer</TD><TD style="TEXT-ALIGN: left">PA</TD><TD style="TEXT-ALIGN: left">16137-4101</TD><TD style="TEXT-ALIGN: left">7246623570</TD><TD style="TEXT-ALIGN: left">9,268.71</TD><TD style="TEXT-ALIGN: left">02/28/11</TD><TD style="TEXT-ALIGN: left">LMW/11</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A5</TD><TD>=IF('Step 1 Raw Data'!A3="","",'Step 1 Raw Data'!A3)</TD></TR><TR><TD>B5</TD><TD>=IF('Step 1 Raw Data'!B3="","",TRIM('Step 1 Raw Data'!B3))</TD></TR><TR><TD>C5</TD><TD>=IF('Step 1 Raw Data'!C3="","",TRIM('Step 1 Raw Data'!C3))</TD></TR><TR><TD>D5</TD><TD>=IF('Step 1 Raw Data'!D3="","",TRIM('Step 1 Raw Data'!D3))</TD></TR><TR><TD>E5</TD><TD>=IF('Step 1 Raw Data'!E3="","",TRIM('Step 1 Raw Data'!E3))</TD></TR><TR><TD>F5</TD><TD>=IF('Step 1 Raw Data'!F3="","",TRIM('Step 1 Raw Data'!F3))</TD></TR><TR><TD>G5</TD><TD>=IF('Step 1 Raw Data'!G3="","",TRIM('Step 1 Raw Data'!G3))</TD></TR><TR><TD>H5</TD><TD>=IF('Step 1 Raw Data'!H3="","",TRIM('Step 1 Raw Data'!H3))</TD></TR><TR><TD>I5</TD><TD>=IF('Step 1 Raw Data'!I3="","",TRIM('Step 1 Raw Data'!I3))*1</TD></TR><TR><TD>J5</TD><TD>=IF('Step 1 Raw Data'!J3="","",TRIM('Step 1 Raw Data'!J3))*1</TD></TR><TR><TD>K5</TD><TD>=IF('Step 1 Raw Data'!K3="","",TRIM('Step 1 Raw Data'!K3))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Alex O]

It’s performing almost exactly as I hoped, my only question is why are the <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:State w:st="on"><st1lace w:st="on">AL</st1lace></st1:State> values being duplicated in the output? For example -1 Asphalt Driveways should be reflected as VSB/09-2375.4, rather than VSB/09-2375.4-2375.40. It’s as if the macro is grabbing the text string from AI and AJ???? AJ can be ignored, as it is a column I was asked to add strictly for auditing purposes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thanks <o></o>

 

[hiker95]

Alex O,

Are you using the ConcatDataV3 macro?

It is only using values from column AI.

 

[Alex O]

hiker95 - if you ever find your way to New Orleans I'm treating to a big bowl of gumbo and a cold Budweiser! This is perfect!!!!

Thanks Again