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

Closed
Report
Posts
3
Registration date
Thursday February 12, 2015
Status
Member
Last seen
February 23, 2015
-
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
-
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?

4 replies

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
3
Registration date
Thursday February 12, 2015
Status
Member
Last seen
February 23, 2015

Thank you for the reply. But it does not seem to be capture all of the rows from the sheets. Even you example the summary only shows 6 total rows even though there are a total of 12 rows (4 rows from each of the three sheets). I would need it to show all the rows even ones with duplicate information.
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.
Posts
3
Registration date
Thursday February 12, 2015
Status
Member
Last seen
February 23, 2015

Hello,

Here is a template I created that I seem to have issues with. I need to show every row that has a Yes on three individual tabs on teh summary page and they will have overlapping tasks. I am not sure why not all the rows with a "Yes" on Project A, Project B, Project C are not appearing on the Summary tab.

https://www.dropbox.com/s/og0kzam95cwu320/Integration%20Project%20Sample.xlsm?dl=0
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
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.