transfer full row based on one column on multiple sheet to oneClosed

Question

I am new to this, and never did codes for Excel. But I need help. I have a workbook with 4 tabs: Summary, Project A, Project B, Project C. On Project A/B/C sheets there is a column called Summary Indicator. In the cell if it is YES, I want the entire row columns A though P to be copied to the Summary tab. The goal is to see only the key rows from all three Project sheets on the Summary tab. Can anyone help a newbie to macros and coding?

vcoolio · Answer

Hello Redwing,

I think that the following code may help:-

Sub CopyData()
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Integer

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name = "Summary" Then GoTo NextSheet
    
    ws.Select
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    For Each cell In Range("A2:A" & lRow)
        If cell.Value = "Yes" Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
            Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
        End If
    Next cell
    
NextSheet:
Next ws

Sheets("Summary").Range("A1:P" & Rows.Count).RemoveDuplicates Columns:=2, Header:=xlYes
Sheets("Summary").Select

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

The "Summary Indicator" is in Column A of each sheet. Type "Yes" beside any row of data (in any sheet) that you want to transfer to the Summary sheet. The criteria ("Yes") is case sensitive.

You can have a look at my test work book here:-

https://www.dropbox.com/s/2272xcox0ng8w75/Redwing35.xlsm?dl=0

to see if it works for you.

Click on the blue "Summarise" button to transfer the data.

Kind regards,
vcoolio.

vcoolio · Answer

Hello again Redwing,

Just in case you want to delete the transferred data from the three project sheets, I've adjusted the code slightly as follows:-

Sub CopyData()
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Integer

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name = "Summary" Then GoTo NextSheet
    
    ws.Select
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    For Each cell In Range("A2:A" & lRow)
        If cell.Value = "Yes" Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).Copy
            Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Range(Cells(cell.Row, "A"), Cells(cell.Row, lCol)).ClearContents
        End If
    Next cell
    Columns("B").SpecialCells(4).EntireRow.Delete
NextSheet:
Next ws

Sheets("Summary").Select

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

You can see it at work in my test work book here:-

https://www.dropbox.com/s/wmshkn76wfuxi8n/Redwing35%282%29.xlsm?dl=0

Cheers,
vcoolio.

vcoolio · Answer

Hello Redwing,

I'm not exactly sure what you are saying. Both of the above codes do exactly what your original post asked, i.e. copy the entire row (A - P) from the Project sheets (A, B & C) to the Summary  sheet once the criteria "Yes" is typed into the Summary Indicator column (A) in any of the Project sheets. The only difference in the codes is that the second one deletes the "used" data from the Project sheets once that data is transferred to the Summary sheet. 
The sample work books currently have "Yes" beside four rows of fictitious data in each Project sheet so twelve rows in total are transferred to the Summary sheet. I'm seeing twelve rows of data in the Summary sheet of both work books once the button is clicked on. In the first work book, all data is left intact in the Project sheets whereas, in the second work book, two rows of data are left behind in each Project sheet (because they do not have "Yes" typed beside them) as the other "used" data is transferred (and then deleted from the Project sheets).
If you type "Yes" in the Summary Indicator column (A) beside all the rows of data, then all the rows of data will be transferred to the Summary sheet. If you type "Yes" beside only one row of data, then only one row will be transferred. How many rows are transferred is up to you. This is in accordance with your original post.

If the code is not working in your actual work book, then I suggest that you upload a sample of your work book (be careful with any sensitive data) so that we can see exactly what's going on. You can upload a sample using a file sharing site such as DropBox.

Regards,
vcoolio.

vcoolio · Answer

Hello Redwing,

Remove this line of code from the macro:-

Sheets("Summary").Range("A1:P" & Rows.Count).RemoveDuplicates Columns:=2, Header:=xlYes

and see if this resolves the issue.

BTW, do you need the "used" or transferred data from the three Project sheets cleared?

Let me know how it goes.

Cheerio,
vcoolio.

Transfer full row based on one column on multiple sheet to one

4 responses

Similar discussions

Subscribe To Our Newsletter!