Pulling rows from multiple sheets and pasting values in appropriate matching headers in a summary sheet.

[kofafa]

Hi there,

I am new to VBA and I need help finding a code to extract data from rows in various worksheets with different names, except for the data sheet. The extracted data needs to be pasted and sorted on a summary sheet located at the beginning of the workbook. To give you an idea, I want the code to automatically extract the corresponding row when any value is added to Column B from any worksheet. It should only extract cells under headers with green color and paste them under matching headers colored green in the summary sheet. Can you please assist me with this? Thank you.

 

[kevin9999]

It's always better if you provide a copy of your sheets using the XL2BB add in so helpers don't have to type out your data layout to test their code. Alternatively, you can share your workbook via Dropbox, Google Drive or similar file sharing platform.
You haven't said what your summary sheet is called, so the code below assumes it's called "Summary" - change this in two places if it's called something else. The code needs to go in the Workbook module, and I assume you know how to do this. If you don't, see this: How to Add Macro Code to Excel Workbook. The code also assumes that you don't have any other worksheets other than the summary sheet, and all the other sheets have the exact same structure as your top image.

Private Sub Workbook_SheetChange(ByVal ws As Object, ByVal Target As Range)
    If ws.Name = "Summary" Then Exit Sub    '<~~ *** Change name if needed ***
    If Target.Cells.CountLarge = 1 And Not Intersect(Target, Columns(2)) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim a, b, InArr, OutArr, i As Long, rng As Range
        With ws
            Set rng = Target.Offset(0, 1).Resize(1, 9)
            ReDim b(1 To 1, 1 To 15)
            InArr = Array(1, 2, 3, 6, 7, 8, 9)
            OutArr = Array(1, 2, 6, 3, 4, 5, 15)
            For i = 0 To 6
                b(1, OutArr(i)) = rng.Cells(1, InArr(i))
            Next i
        End With
        With Worksheets("Summary")          '<~~ *** Change name if needed ***
            .Range("C" & .Cells(Rows.Count, "C").End(xlUp).Row + 1).Resize(1, 15).Value = b
        End With
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[kofafa]

Hello Kevin999,
Thank you so much for your help!! Much appreciated! I copied and pasted the code in the workbook module. unfortunately, I'm unable to run it. Please help.

 

[kevin9999]

It's probably easier if I show you. This is a link to the file with the workbook code in the correct place: kofafa.xlsm

 

[kofafa]

Hi Kevin,

I am grateful for your assistance! May I request further help from you? I would like to incorporate a button on the summary page which, when clicked, will populate the summary page instead of auto-populating it.

Thank you!

 

[kevin9999]

Do you mean populate the summary sheet with all available data from all the worksheets in one go?

 

[kofafa]

Yes, that's correct. I'm attempting to send my workbook, but my Excel program keeps disabling the XL2bb.

 

[kevin9999]

Could you share your workbook via Dropbox, Google Drive or similar file sharing platform?

 

[kofafa]

Google Drive: Sign-in

Here is my workbook. I have also added some formulas in certain cells.

 

[kevin9999]

Google Drive: Sign-in

Here is my workbook. I have also added some formulas in certain cells.

Thank you, I'll look at it soon.