Pulling selected rows from multiple worksheets to a destination worksheet

[saifrusho]

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.

Excel 2010
	A	B	C	D	E	F	G	H
3	Code	Sl.	Code Provision	Yes/No	Findings Explanation	Severity	Corrective Action	Completion Date
4	A1 - Freely Chosen Employment	A1.1	Any type of forced, involuntary prison, indentured, bonded (including debt bondage), trafficked or slave labor is not used
5	A1 - Freely Chosen Employment	A1.2	Adequate and effective policy and procedures are established ensuring that any form of forced, bonded involuntary prison, trafficked or slave labor is not used.
6	A1 - Freely Chosen Employment	A1.3	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.
7	A1 - Freely Chosen Employment	A1.4	Upon hiring, the workers government issued identification and personal documentation originals are not held by employer /labor agent/contractor (if applicable).
A. LABOR

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.

 

[JLGWhiz]

Give this a try

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]

Give this a try

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]

Try this

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]

Try this

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]

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]

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.

 

[JLGWhiz]

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

You're welcome,
Regards, JLG