Pulling selected rows from multiple worksheets to a destination worksheet

saifrusho

New Member
Joined
May 21, 2017
Messages
8
Hey all!

Seriously struggling here. See if you can help this poor soul out!


What I am looking for:

There are 7 worksheets in this book, 5 of which have same column headings (second to sixth worksheets). I want to pull selected rows from these 5 worksheets into the 7th worksheet (CAP) while not disturbing the entries in any of the 5 worksheets (entries in these 5 worksheets should stay as is).

I am attaching one of the worksheets (A. LABOR). There are 4 more named (B. HEALTH & SAFETY, C. ENVIRONMENT, D. ETHICS, E. MANAGEMENT SYSTEM). Any row in the five worksheets with 'NO' selected in column D should go to a seventh worksheet (named CAP). But column D data itself should not go. And every time I run the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(38, 38, 38); font-family: Verdana; text-size-adjust: auto;">VBA</acronym>, the process should repeat as fresh (not adding to the previously run <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(38, 38, 38); font-family: Verdana; text-size-adjust: auto;">VBA</acronym>). I also want the exported rows in CAP worksheet to be 'wrapped' so that all texts show properly.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Sl.</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Code Provision</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Yes/No</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Findings Explanation</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Severity</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Corrective Action</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCFFDC;;">Completion Date</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9E1F2;;">A1 - Freely Chosen Employment</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;background-color: #F2F2F2;;">A1.1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Any type of forced, involuntary prison, indentured, bonded (including debt bondage), trafficked or slave labor is not used</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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9E1F2;;">A1 - Freely Chosen Employment</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;background-color: #F2F2F2;;">A1.2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Adequate and effective policy and procedures are established ensuring that any form of forced, bonded involuntary prison, trafficked or slave labor is not used.</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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9E1F2;;">A1 - Freely Chosen Employment</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;background-color: #F2F2F2;;">A1.3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Terms of contract are provided in writing and in their native language prior to employment (in case of migrant workers, before they leave their home country/region) of the key employment terms and conditions via employment letter/agreement/contract as required by law and explained verbally in their native language so workers understand what the contract states.  </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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D9E1F2;;">A1 - Freely Chosen Employment</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;background-color: #F2F2F2;;">A1.4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Upon hiring, the worker’s government issued identification and personal documentation originals are not held by employer /labor agent/contractor (if applicable).  </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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">A. LABOR</p><br /><br />

I saw a similar one posted here:



https://www.mrexcel.com/forum/excel-...ary-sheet.html

But 1) I could not quite apply it to all 5 of my needed worksheets and 2) if I run it twice, it adds to the previously run data instead of creating afresh.

Any help would be highly appreciated.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,048
Office Version
2013
Platform
Windows
Give this a try

Code:
Sub copyStuff()
Dim shAry As Variant, i As Long
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY")) ', Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
    For i = LBound(shAry) To UBound(shAry)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        With Sheets("CAP")
            .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).ClearContents
        End With
        shAry(i).AutoFilterMode = False
    Next
End Sub
 

saifrusho

New Member
Joined
May 21, 2017
Messages
8
Give this a try

Code:
Sub copyStuff()
Dim shAry As Variant, i As Long
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY")) ', Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
    For i = LBound(shAry) To UBound(shAry)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        With Sheets("CAP")
            .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).ClearContents
        End With
        shAry(i).AutoFilterMode = False
    Next
End Sub

Great! This works nicely. Just a small issue: it exports the entries to the CAP sheet alright but if I run the VBA a second time, the new ones just gets added to the entries from first round. I would prefer that it creates a fresh list every time I run the VBA (since it's a report rather than a database, I will make changes to the 'Yes/No' column few times and then would like to create the summary list afresh. Can you suggest a way to achieve that? Also, I would prefer that the column D does not go to the summary sheet (CAP).

Big help already!!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,048
Office Version
2013
Platform
Windows
Try this

Code:
Sub copyStuff2()
Dim shAry As Variant, i As Long, rng As Range
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY"), Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
Sheets("CAP").Range("A2", Sheets("CAP").Cells(Rows.Count, 1)).EntireRow.ClearContents
    For i = LBound(shAry) To UBound(shAry)
        Set rng = Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy rng
        With Sheets("CAP")
            .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).ClearContents
        End With
        shAry(i).AutoFilterMode = False
    Next
End Sub
 

saifrusho

New Member
Joined
May 21, 2017
Messages
8
Try this

Code:
Sub copyStuff2()
Dim shAry As Variant, i As Long, rng As Range
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY"), Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
Sheets("CAP").Range("A2", Sheets("CAP").Cells(Rows.Count, 1)).EntireRow.ClearContents
    For i = LBound(shAry) To UBound(shAry)
        Set rng = Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy rng
        With Sheets("CAP")
            .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).ClearContents
        End With
        shAry(i).AutoFilterMode = False
    Next
End Sub

Works like a charm! Thanks again.
Just one more little thing: while running the VBA, it is exporting empty column D to the destination sheet (CAP). It is good that it is not showing the 'Yes or No' option but I do not want this column to be exported at all. That is, in CAP sheet column D would be 'Findings Explanation' instead of an empty column originally for 'Yes/No' in other worksheets.

I have also added a button on a different worksheet to run this VBA and I want the button click to take me to the destination worksheet (CAP sheet). Is there a way to do that?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,048
Office Version
2013
Platform
Windows
Code:
Sub copyStuff3()
Dim shAry As Variant, i As Long, rng As Range
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY"), Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
Sheets("CAP").Range("A2", Sheets("CAP").Cells(Rows.Count, 1)).EntireRow.ClearContents
    For i = LBound(shAry) To UBound(shAry)
        Set rng = Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy rng
        shAry(i).AutoFilterMode = False
    Next
    With Sheets("Cap")
        .Columns(4).Delete
        .Select
    End With
End Sub
 

saifrusho

New Member
Joined
May 21, 2017
Messages
8
Code:
Sub copyStuff3()
Dim shAry As Variant, i As Long, rng As Range
shAry = Array(Sheets("LABOR"), Sheets("HEALTH & SAFETY"), Sheets("ENVIRONMENT"), Sheets("ETHICS"), Sheets("MANAGEMENT SYSTEM"))
Sheets("CAP").Range("A2", Sheets("CAP").Cells(Rows.Count, 1)).EntireRow.ClearContents
    For i = LBound(shAry) To UBound(shAry)
        Set rng = Sheets("CAP").Cells(Rows.Count, 1).End(xlUp)(2)
        shAry(i).UsedRange.AutoFilter 4, "NO"
        shAry(i).UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy rng
        shAry(i).AutoFilterMode = False
    Next
    With Sheets("Cap")
        .Columns(4).Delete
        .Select
    End With
End Sub

SOLVED! Thanks again. All worked fine. Quite happy for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,785
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top