VBA Macro to help clean up report

[pkaskaggs]

I am trying to rearrange a report so it can be used in a differnt department. They only need certain information, and one column of info contains a five digit code that needs to be translated into an address.

I would like to create a macro that will delete all unwanted columns, Then insert a column next to the five digit address codes, and insert the address, the code stands for. It would also be ok for the address to replace the five digit code. Finally I would like to delete an entire row if one of the columns contains certain information that I will specify.

Summary
Delete columns:2,3,5,10,11,12,13,14,15,16,17,18,19,20

(Add column next to what will be 6 after the others are deleted {6 Contains the 5 Digit codes} Output Corrisponding address) or (Replace Five digit Code with specified address)

Delete Rows that contain specified data in Column 25

I hope this is easy enough to understand

Thanks for any help
Bryan

 

[hiker95]

pkaskaggs,

Welcome to the MrExcel forum.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[pkaskaggs]

This is original
PIPELINE

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 129px"><COL style="WIDTH: 67px"><COL style="WIDTH: 100px"><COL style="WIDTH: 90px"><COL style="WIDTH: 97px"><COL style="WIDTH: 77px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 69px"><COL style="WIDTH: 107px"><COL style="WIDTH: 86px"><COL style="WIDTH: 72px"><COL style="WIDTH: 126px"><COL style="WIDTH: 111px"><COL style="WIDTH: 135px"><COL style="WIDTH: 68px"><COL style="WIDTH: 123px"><COL style="WIDTH: 152px"><COL style="WIDTH: 140px"><COL style="WIDTH: 150px"><COL style="WIDTH: 142px"><COL style="WIDTH: 131px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 146px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><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><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TFDM5F17AX015641</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8359</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SA</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42267</TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">U</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">3473</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">T</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004646476</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010/07/06</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Ready</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">BC</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">55</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004517632</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/20</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SF</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">56</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004515827</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDKN3DU0A5082535</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1225</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SF</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004531220</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/11/23</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">PR</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004533408</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/03</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/21</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">4T1BF3EK5AU089590</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2532</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">FLTBB</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">F</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">LA001</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">04025</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">U</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">195</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">H</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G9</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">V2010033663C</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2009/11/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8185814</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">1NXBU4EE3AZ321173</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">FR</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">-7</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">E</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004543319</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/29</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2010/01/01</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010/01/15</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I would like it to look like this once macro has been run.

PIPELINE

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 129px"><COL style="WIDTH: 67px"><COL style="WIDTH: 97px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 78px"><COL style="WIDTH: 126px"><COL style="WIDTH: 150px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><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="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TFDM5F17AX015641</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8359</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDKN3DU0A5082535</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1225</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">4T1BF3EK5AU089590</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2532</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">F</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">LA001</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">04025</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Dallas, TX</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2009/11/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">1NXBU4EE3AZ321173</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2010/01/01</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est Comp dt</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

It doesnt matter if the Location replaces the five digit code instead of creating a new column. Also I would like to delete entire rows if Column "K" contains a certain code "Shipped dt".

Thanks again!!

 

[pkaskaggs]

The code to delete rows, that contain data I dont want, is working. Now all I need is to delete the unwanted columns, and find a way to replace Row "F" with the address for the corresponding code. Basically an alternative to Vlookup.

 

[hiker95]

pkaskaggs,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
13	5TFDM5F17AX015641	8359	2010	SA	HO	42267				U		3473	M			R	T	4646476	7/6/2010	1/2/2001				1/2/2001	Est VPC Arr	Ready
14	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Rev Comp dt	Locked
15	JTDBT4K32A1358131	1441	2010	SF	HO	42103			42980	A		56	R		R	G	N	4515827		10/22/2009				5/12/2010	Rev Comp dt	Locked
16	JTDKN3DU0A5082535	1225	2010	SF	HO	42103			42980	A		32	R	K	R	G	N	4531220		11/23/2009				6/5/2010	Rev Comp dt	Locked
17	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Rev Comp dt	Locked
18	4T1BF3EK5AU089590	2532	2010	GT	GT	FLTBB	F	LA001	4025	U		195	H	M		G9	N	V2010033663C	12/2/2009	12/24/2009	11/30/2009	12/15/2009	8185814	12/24/2009	Est VPC Arr
19	1NXBU4EE3AZ321173	1838	2010	FR	HO	42103			42980	A		-7	R	K	R	E	N	4543319	12/22/2009	12/29/2009	1/1/2010	1/15/2010		6/22/2010	Est Comp dt	Locked
20	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Shipped dt	Locked
21	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Shipped dt	Locked
22
PIPELINE

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
13	5TFDM5F17AX015641	SA	42267								1/2/2001	Est VPC Arr	Ready
14	3TMJU4GN1AM095989	BC	42103			42980					5/13/2010	Rev Comp dt	Locked
15	JTDBT4K32A1358131	SF	42103			42980					5/12/2010	Rev Comp dt	Locked
16	JTDKN3DU0A5082535	SF	42103			42980					6/5/2010	Rev Comp dt	Locked
17	5TDKK4CC2AS343200	PR	42103			42980					6/5/2010	Rev Comp dt	Locked
18	4T1BF3EK5AU089590	GT	FLTBB	F	LA001	4025		11/30/2009	12/15/2009	8185814	12/24/2009	Est VPC Arr
19	1NXBU4EE3AZ321173	FR	42103			42980		1/1/2010	1/15/2010		6/22/2010	Est Comp dt	Locked
20
PIPELINE

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("PIPELINE")
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("J:T").EntireColumn.Delete
  .Columns("E").EntireColumn.Delete
  .Columns("B:C").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
  If .Range("L12") = "" Then .Range("L12") = "Test"
  With .Range("L12:L" & LR)
    .AutoFilter Field:=1, Criteria1:="=Shipped dt", Operator:=xlAnd
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Resize(LR).EntireRow.Delete
    .AutoFilter
  End With
  If .Range("L12") = "Test" Then .Range("L12") = ""
End With
Application.ScreenUpdating = True
End Sub

Then run the "ReordData" macro.


If you gave me the formula for cell G13, I could insert the formula for you.

 

[hiker95]

pkaskaggs,

Let me adjust the macro to delete/insert columns per your latest screenshots (I deleted columns from the right to left).

 

[pkaskaggs]

pkaskaggs,




If you gave me the formula for cell G13, I could insert the formula for you.

This is a great start thanks!!! Can you incorperate this into your formula?

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("y1", Range("y" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Shipped dt*"
.AutoFilter 1, "*Inv dt*"
.AutoFilter 1, "*Est Comp dt*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

It is eaiser for me to add and remove the information myself as we decide what to keep and delete.

As far as column "G" is concerned, I dont have a formula, I was hoping for a generic VBA code that I could change the numbers in "F" and the ouput in "G". Then duplicate for as many address codes as we have. I think there are 1500 unique codes that stand for address.

The report only exports the codes, I want the macro to display the address in the adjecent cell. I manually typed in the City State for the example.

 

[hiker95]

Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
13	5TFDM5F17AX015641	8359	2010	SA	HO	42267				U		3473	M			R	T	4646476	7/6/2010	1/2/2001				1/2/2001	Est VPC Arr	Ready
14	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Rev Comp dt	Locked
15	JTDBT4K32A1358131	1441	2010	SF	HO	42103			42980	A		56	R		R	G	N	4515827		10/22/2009				5/12/2010	Rev Comp dt	Locked
16	JTDKN3DU0A5082535	1225	2010	SF	HO	42103			42980	A		32	R	K	R	G	N	4531220		11/23/2009				6/5/2010	Rev Comp dt	Locked
17	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Rev Comp dt	Locked
18	4T1BF3EK5AU089590	2532	2010	GT	GT	FLTBB	F	LA001	4025	U		195	H	M		G9	N	V2010033663C	12/2/2009	12/24/2009	11/30/2009	12/15/2009	8185814	12/24/2009	Est VPC Arr
19	1NXBU4EE3AZ321173	1838	2010	FR	HO	42103			42980	A		-7	R	K	R	E	N	4543319	12/22/2009	12/29/2009	1/1/2010	1/15/2010		6/22/2010	Est Comp dt	Locked
20	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Shipped dt	Locked
21	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Shipped dt	Locked
22
PIPELINE

After the maceo:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
13	5TFDM5F17AX015641	8359	HO							1/2/2001	Est VPC Arr
14	3TMJU4GN1AM095989	7188	HO			42980		K		5/13/2010	Rev Comp dt
15	JTDBT4K32A1358131	1441	HO			42980				5/12/2010	Rev Comp dt
16	JTDKN3DU0A5082535	1225	HO			42980		K		6/5/2010	Rev Comp dt
17	5TDKK4CC2AS343200	5328	HO			42980		K		6/5/2010	Rev Comp dt
18	4T1BF3EK5AU089590	2532	GT	F	LA001	4025		M	11/30/2009	12/24/2009	Est VPC Arr
19	1NXBU4EE3AZ321173	1838	HO			42980		K	1/1/2010	6/22/2010	Est Comp dt
20
PIPELINE

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("PIPELINE")
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("Z").EntireColumn.Delete
  .Columns("V:W").EntireColumn.Delete
  .Columns("O:T").EntireColumn.Delete
  .Columns("J:M").EntireColumn.Delete
  .Columns("F").EntireColumn.Delete
  .Columns("C:D").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
  If .Range("K12") = "" Then .Range("K12") = "Test"
  With .Range("K12:K" & LR)
    .AutoFilter Field:=1, Criteria1:="=Shipped dt", Operator:=xlAnd
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Resize(LR).EntireRow.Delete
    .AutoFilter
  End With
  If .Range("K12") = "Test" Then .Range("K12") = ""
End With
Application.ScreenUpdating = True
End Sub

Then run the "ReordData" macro.

 

[hiker95]

Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
13	5TFDM5F17AX015641	8359	2010	SA	HO	42267				U		3473	M			R	T	4646476	7/6/2010	1/2/2001				1/2/2001	Est VPC Arr	Ready
14	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Rev Comp dt	Locked
15	JTDBT4K32A1358131	1441	2010	SF	HO	42103			42980	A		56	R		R	G	N	4515827		10/22/2009				5/12/2010	Rev Comp dt	Locked
16	JTDKN3DU0A5082535	1225	2010	SF	HO	42103			42980	A		32	R	K	R	G	N	4531220		11/23/2009				6/5/2010	Rev Comp dt	Locked
17	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Rev Comp dt	Locked
18	4T1BF3EK5AU089590	2532	2010	GT	GT	FLTBB	F	LA001	4025	U		195	H	M		G9	N	V2010033663C	12/2/2009	12/24/2009	11/30/2009	12/15/2009	8185814	12/24/2009	Est VPC Arr
19	1NXBU4EE3AZ321173	1838	2010	FR	HO	42103			42980	A		-7	R	K	R	E	N	4543319	12/22/2009	12/29/2009	1/1/2010	1/15/2010		6/22/2010	Est Comp dt	Locked
20	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Shipped dt	Locked
21	5TDKK4CC2AS343200	5328	2010	PR	HO	42103			42980	A		32	R	K	R	G	N	4533408	12/3/2009	12/21/2009				6/5/2010	Shipped dt	Locked
22	5TFDM5F17AX015641	8359	2010	SA	HO	42267				U		3473	M			R	T	4646476	7/6/2010	1/2/2001				1/2/2001	Inv dt	Ready
23	3TMJU4GN1AM095989	7188	2010	BC	HO	42103			42980	A		55	R	K	R	G	N	4517632	10/12/2009	10/20/2009				5/13/2010	Est Comp dt	Locked
24
PIPELINE

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
13	5TFDM5F17AX015641	8359	HO							1/2/2001	Est VPC Arr
14	3TMJU4GN1AM095989	7188	HO			42980		K		5/13/2010	Rev Comp dt
15	JTDBT4K32A1358131	1441	HO			42980				5/12/2010	Rev Comp dt
16	JTDKN3DU0A5082535	1225	HO			42980		K		6/5/2010	Rev Comp dt
17	5TDKK4CC2AS343200	5328	HO			42980		K		6/5/2010	Rev Comp dt
18	4T1BF3EK5AU089590	2532	GT	F	LA001	4025		M	11/30/2009	12/24/2009	Est VPC Arr
19
PIPELINE

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Application.ScreenUpdating = False
With ActiveSheet
  .AutoFilterMode = False
  With .Range("Y1", .Range("Y" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="=*Shipped dt*", Operator:=xlOr, Criteria2:="=*Inv dt*"
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    .AutoFilter Field:=1, Criteria1:="=*Est Comp dt*", Operator:=xlAnd
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
  End With
  .AutoFilterMode = False
  .Columns("Z").EntireColumn.Delete
  .Columns("V:W").EntireColumn.Delete
  .Columns("O:T").EntireColumn.Delete
  .Columns("J:M").EntireColumn.Delete
  .Columns("F").EntireColumn.Delete
  .Columns("C:D").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
End With
Application.ScreenUpdating = True
End Sub

 

[pkaskaggs]

This formula works great, however if I paste the data into a blank workbook and try to run the macro I get a Run-time error '9':
Subscript out of range.

I am trying to automate it so it can be run easily multiple times a day. The report is exported from mainframe to excel. I was planning on having a blank template that coworkers could paste the information into everytime they exported then run the macro. Will this work?