Copy entire row to another worksheet if column = specific value

michepps

New Member
Joined
Sep 4, 2014
Messages
1
Hi. I am in need of a macro or formula that will copy an entire row over to another worksheet based on a specific value. For instance. I have the below table:
PCRDevice #Location AddressTest DateAOC Due DateThird Party WitnessElevator PartViolation ConditionSuggested RemedyClassification
(Building, Billable, Maintenance, Repair)
6B12W12080231 W. 246th ST, Bronx 8/21/201211/13/2012Lift - Tech LTDCar Door/Gate ContactInsufficentAdjustMaintenance
21B12W12080231 W. 246th ST, Bronx 8/21/201211/13/2012Lift - Tech LTDInterlocksInsufficentAdjustMaintenance
5J12P10193231 W. 246th ST, Bronx 8/21/201211/13/2012Lift - Tech LTDCar Door/GateMisalignedAdjustBillable
69M72P10193231 W. 246th ST, Bronx 8/21/201211/13/2012Lift - Tech LTDCode Data PlateMissingProvideBillable

<TBODY>
</TBODY><COLGROUP><COL><COL span=2><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

I want to copy (not cut) the entire row to a new worksheet when the last column, L, = "Billable". Same for when it = "Maintenance", etc...
I would be adding to this spreadsheet every day so i would need all the rows to be copied over to the same worksheet each time the macro is ran.

I am not familiar with macros at all so any help/guidance anyone can provide would be greatly appreciated.

Thanks
Michelle
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi. I am in need of a macro or formula that will copy an entire row over to another worksheet based on a specific value. For instance. I have the below table:…….
Hi Michelle,
.. I changed your data to make the Macro I wrote for you a bit easier to test:


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">P</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;color: #333333;;">Device #</td><td style="text-align: center;color: #333333;;">Location Address</td><td style="text-align: center;color: #333333;;">Test Date</td><td style="text-align: center;color: #333333;;">AOC Due Date</td><td style="text-align: center;color: #333333;;">Third Party Witness</td><td style="text-align: center;color: #333333;;">Elevator Part</td><td style="text-align: center;color: #333333;;">Violation Condition</td><td style="text-align: center;color: #333333;;">Suggested Remedy</td><td style="text-align: center;color: #333333;;">Classification (Building, Billable, Maintenance, Repair)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">6</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">1</td><td style="text-align: center;color: #333333;;">2W12080</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Car Door/Gate Contact</td><td style="text-align: center;color: #333333;;">Insufficent</td><td style="text-align: center;color: #333333;;">Adjust</td><td style="text-align: center;color: #333333;;">Maintenance</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">21</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">1</td><td style="text-align: center;color: #333333;;">2W12080</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Interlocks</td><td style="text-align: center;color: #333333;;">Insufficent</td><td style="text-align: center;color: #333333;;">Adjust</td><td style="text-align: center;color: #333333;;">Maintenance</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">5</td><td style="text-align: center;color: #333333;;">J</td><td style="text-align: center;color: #333333;;">1</td><td style="text-align: center;color: #333333;;">2P10193</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Car Door/Gate</td><td style="text-align: center;color: #333333;;">Misaligned</td><td style="text-align: center;color: #333333;;">Adjust</td><td style="text-align: center;color: #333333;;">Repair</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">69</td><td style="text-align: center;color: #333333;;">M</td><td style="text-align: center;color: #333333;;">7</td><td style="text-align: center;color: #333333;;">2P10193</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Code Data Plate</td><td style="text-align: center;color: #333333;;">Missing</td><td style="text-align: center;color: #333333;;">Provide</td><td style="text-align: center;color: #333333;;">Billable</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">c</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">414</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">232</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">a</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">fa</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">hz</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Building</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">d</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">415</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">233</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">b</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">asfgg</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">aaad</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Repair</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">e</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">416</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">234</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">c</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">gfhgj</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">mnm</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Building</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">f</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">417</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">235</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">d</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">wrw</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">eeqw</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Billable</td></tr></tbody></table><p style="width:7,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">FullDataSheet</p><br /><br />

. After running the macro I wrote, new sheets are added that look like this


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">P</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;color: #333333;;">Device #</td><td style="text-align: center;color: #333333;;">Location Address</td><td style="text-align: center;color: #333333;;">Test Date</td><td style="text-align: center;color: #333333;;">AOC Due Date</td><td style="text-align: center;color: #333333;;">Third Party Witness</td><td style="text-align: center;color: #333333;;">Elevator Part</td><td style="text-align: center;color: #333333;;">Violation Condition</td><td style="text-align: center;color: #333333;;">Suggested Remedy</td><td style="text-align: center;color: #333333;;">Classification (Building, Billable, Maintenance, Repair)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">c</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">414</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">232</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">a</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">fa</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">hz</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Building</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">e</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">416</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">234</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">c</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">gfhgj</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">mnm</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Building</td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Building</p><br /><br />


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">P</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;color: #333333;;">Device #</td><td style="text-align: center;color: #333333;;">Location Address</td><td style="text-align: center;color: #333333;;">Test Date</td><td style="text-align: center;color: #333333;;">AOC Due Date</td><td style="text-align: center;color: #333333;;">Third Party Witness</td><td style="text-align: center;color: #333333;;">Elevator Part</td><td style="text-align: center;color: #333333;;">Violation Condition</td><td style="text-align: center;color: #333333;;">Suggested Remedy</td><td style="text-align: center;color: #333333;;">Classification (Building, Billable, Maintenance, Repair)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">69</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">M</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">7</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">2P10193</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Code Data Plate</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Missing</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Provide</td><td style="text-align: center;color: #333333;;">Billable</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">f</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">417</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">235</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">d</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">wrw</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">eeqw</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">Billable</td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Billable</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">P</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;color: #333333;;">Device #</td><td style="text-align: center;color: #333333;;">Location Address</td><td style="text-align: center;color: #333333;;">Test Date</td><td style="text-align: center;color: #333333;;">AOC Due Date</td><td style="text-align: center;color: #333333;;">Third Party Witness</td><td style="text-align: center;color: #333333;;">Elevator Part</td><td style="text-align: center;color: #333333;;">Violation Condition</td><td style="text-align: center;color: #333333;;">Suggested Remedy</td><td style="text-align: center;color: #333333;;">Classification (Building, Billable, Maintenance, Repair)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">5</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">J</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">1</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">2P10193</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Car Door/Gate</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Misaligned</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Adjust</td><td style="text-align: center;border-bottom: 1px solid black;color: #333333;;">Repair</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">d</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">415</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">233</td><td style="text-align: center;border-left: 1px solid black;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;border-right: 1px solid black;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">b</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">asfgg</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">aaad</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Repair</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Repair</p><br /><br />


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">P</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;color: #333333;;">Device #</td><td style="text-align: center;color: #333333;;">Location Address</td><td style="text-align: center;color: #333333;;">Test Date</td><td style="text-align: center;color: #333333;;">AOC Due Date</td><td style="text-align: center;color: #333333;;">Third Party Witness</td><td style="text-align: center;color: #333333;;">Elevator Part</td><td style="text-align: center;color: #333333;;">Violation Condition</td><td style="text-align: center;color: #333333;;">Suggested Remedy</td><td style="text-align: center;color: #333333;;">Classification (Building, Billable, Maintenance, Repair)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">6</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">1</td><td style="text-align: center;color: #333333;;">2W12080</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Car Door/Gate Contact</td><td style="text-align: center;color: #333333;;">Insufficent</td><td style="text-align: center;color: #333333;;">Adjust</td><td style="text-align: center;color: #333333;;">Maintenance</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">21</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">1</td><td style="text-align: center;color: #333333;;">2W12080</td><td style="text-align: center;color: #333333;;">231 W. 246th ST, Bronx</td><td style="text-align: center;color: #333333;;">8/21/2012</td><td style="text-align: center;color: #333333;;">11/13/2012</td><td style="text-align: center;color: #333333;;">Lift - Tech LTD</td><td style="text-align: center;color: #333333;;">Interlocks</td><td style="text-align: center;color: #333333;;">Insufficent</td><td style="text-align: center;color: #333333;;">Adjust</td><td style="text-align: center;color: #333333;;">Maintenance</td></tr></tbody></table><p style="width:6,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Maintenance</p><br /><br />


…..
I would be adding to this spreadsheet every day so i would need all the rows to be copied over to the same worksheet each time the macro is ran…..
. Each time you run the code all the new sheets are wiped out and the process starts again. So simply update your fill Data sheet and run the program again.

. Here is the code:

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">'Not necerssary but good practice to keep computer memery usage to minimum (and helps show up errors)</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> MicheppsAdvFiltZuNeuTab()<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Not necerssary but speeds things up a bit, by turning screen upating off.</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd <SPAN style="color:#007F00">'If anything goes wrong go to the End instead of crashing</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet <SPAN style="color:#007F00">'ws now has Methods and Properties of Worksheets obtained with . dot</SPAN><br><SPAN style="color:#007F00">'Start Bit to  Delete Sheets / Tabs------------</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Prevents being asked everytime if you really want to delete the Workbook</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>    <SPAN style="color:#00007F">If</SPAN> ws.Name <> "FullDataSheet" <SPAN style="color:#00007F">Then</SPAN><br>    ws.Delete<br>    <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'Presumably then the worksheet name is FullDataSheet s0</SPAN><br>    <SPAN style="color:#007F00">' do nothing (Don't delete it!)</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Turn it back on</SPAN><br><SPAN style="color:#007F00">'End Bit to delete new Sheets / Tabs------------</SPAN><br><br><SPAN style="color:#007F00">'Add new Worksheets---</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Classification <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">'Classification name, not kept constant, used / updated in looping</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LastClassificationRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br><SPAN style="color:#00007F">Let</SPAN> Worksheets.Add(After:=Worksheets(1)).Name = "Unique1" <SPAN style="color:#007F00">'Add a Worksheet after the first, named Unique1 for now</SPAN><br><SPAN style="color:#00007F">Let</SPAN> LastClassificationRow = Sheets("FullDataSheet").Range("L" & Rows.Count).End(xlUp).Row<br>Sheets("FullDataSheet").Range("L1:L" & LastClassificationRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Unique1").Range("A1"), Unique:=<SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Copies entire L Column to first column in sheet2 (Tempory made "Unique1" sheet), The important bit is Unique:=True - that only copies unique bits</SPAN><br><SPAN style="color:#007F00">'---------------------</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> LastUnqRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, UqeRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Rows in tempory Unique sheet</SPAN><br><SPAN style="color:#00007F">Let</SPAN> LastUnqRow = Worksheets("Unique1").Cells.Find(What:="*", After:=Worksheets("Unique1").Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row <SPAN style="color:#007F00">'Get last Unique Row for use in next loop</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> UqeRow = 2 <SPAN style="color:#00007F">To</SPAN> LastUnqRow <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">'</SPAN><br>    <SPAN style="color:#007F00">'Make new sheet------------</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Sheets("Unique1").Cells(UqeRow, 1).Text <> "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Assuming a team is there</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> Classification = Sheets("Unique1").Cells(UqeRow, 1).Text <SPAN style="color:#007F00">'Put name in Classification variable</SPAN><br>    <SPAN style="color:#00007F">Let</SPAN> Worksheets.Add(After:=Worksheets(1)).Name = Classification <SPAN style="color:#007F00">'Add new worksheet with Classification name</SPAN><br>    <br>    <br>      <SPAN style="color:#00007F">With</SPAN> Sheets("FullDataSheet") <SPAN style="color:#007F00">'Copying data to new sheet----</SPAN><br>        .UsedRange.AutoFilter Field:=12, Criteria1:=Classification <SPAN style="color:#007F00">'Filter out everything except with that with the appropriate Classification (makes visible based on the criteria only the stuff you want??)....</SPAN><br>        .UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(Classification).Range("A1") <SPAN style="color:#007F00">', then combine it with SpecialCells to just copy that wot you see, (and then send it to the relavent new sheet , name n).. ( Idid notice that it works the same without the .SpecialCells(xlCellTypeVisible) bit, - but that mayjust be Excel “guessing wot you want” as it does, that is to say it copies by default wot is visible?- not too sure on that one yet.)</SPAN><br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN> <SPAN style="color:#007F00">'-------------------------------------------------</SPAN><br>    <br>      <SPAN style="color:#00007F">With</SPAN> Sheets(Classification).UsedRange <SPAN style="color:#007F00">'Bit of simple Format Tidying up</SPAN><br>        .WrapText = <SPAN style="color:#00007F">False</SPAN><br>        .Columns.AutoFit<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>    <SPAN style="color:#007F00">'Do nothing if no Classification given</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'-----------------------------</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> UqeRow <SPAN style="color:#007F00">'Go back and make another ner sheet</SPAN><br><br>Sheets("FullDataSheet").AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br><br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">'Prevent being asked if you really want to delete Temporary Unique sheet</SPAN><br>Sheets("Unique1").Delete <SPAN style="color:#007F00">' delete the filtered Classification name sheet as you do not need it any more</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br>TheEnd:<br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Important to do this here so if anything goes wron then the screen updating is turned back on, ohterwisee the screen is dead</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'MicheppsAdvFiltZuNeuTab()</SPAN></FONT>

. It is in the Sheet Module of Sheet “FullDataSheet” in the following two Files. One File is before and the other after running the macro.
FileSnack | Easy file sharing
FileSnack | Easy file sharing

…….
I am not familiar with macros at all so any help/guidance anyone can provide would be greatly appreciated.

Thanks
Michelle
. If you need any more help in getting started, or have any other questions, then get back.

Alan

P.s. 1. The sizes (Rows / Columns etc.) are all limited to about 255 initially but that can easily be changed.

P.s. 2. Full credit to Alan_P for the code. The important bits I stole from his code in Thread http://www.mrexcel.com/forum/excel-questions/799667-copying-row-based-coloumn-contents-2.html
. You should go through that as the code is discussed and developed in detail.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,728
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top