Fill a sheet with data from three different sheets, same workbook, in a very tricky way

[Kelly mort]

Cross posted at:
https://www.mrexcel.com/board/thread...y-way.1120503/




I have this challenge here again.

I am pulling data from three sheets: "Group 1", " Group 2" and "Group 3".

My goal is to have the data from all three sheets displayed on another sheet as shown in the sample workbook

Reason for cross posting:
It has been a few days without solution

[Pepe Le Mokko]

Hi
please post your sheet on the forum ( see yellow banner)
External links are notoriously unsafe
Thank you

[Kelly mort]

Here we go:

[Marc L]

With the item entry in OUT_PUT_DATA!B1 cell,

a VBA beginner starter demonstration to paste to the (OUT_PUT_DATA) worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim K, H, L, N&, V, R&, C%
        If Target.Address <> "$B$1" Then Exit Sub
        Application.EnableEvents = False
        Me.UsedRange.Offset(1).Clear
        If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
        K = [{2,3,4,15}]
        H = Application.Index(Worksheets(1).UsedRange.Rows(1), , K)
        L = Application.Index(Worksheets(1).UsedRange.Rows(1), , [{16,16,17,17}])
    For N = 1 To Me.Index - 1
        With Sheets(N).UsedRange
                V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
            For C = 5 To 13 Step 2
                If IsEmpty(.Cells(V, C)) Then Exit For
                R = R + 1
                Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
            Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L
            End If
        End With
    Next
        Application.EnableEvents = True
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Kelly mort]

This part of the code is what I don't understand, I want you to explain them to me. Thanks again.

                V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
            For C = 5 To 13 Step 2
                If IsEmpty(.Cells(V, C)) Then Exit For
                R = R + 1
                Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
            Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L

[Kelly mort]

With the item entry in OUT_PUT_DATA!B1 cell,

a VBA beginner starter demonstration to paste to the (OUT_PUT_DATA) worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim K, H, L, N&, V, R&, C%
        If Target.Address <> "$B$1" Then Exit Sub
        Application.EnableEvents = False
        Me.UsedRange.Offset(1).Clear
        If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
        K = [{2,3,4,15}]
        H = Application.Index(Worksheets(1).UsedRange.Rows(1), , K)
        L = Application.Index(Worksheets(1).UsedRange.Rows(1), , [{16,16,17,17}])
    For N = 1 To Me.Index - 1
        With Sheets(N).UsedRange
                V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
            For C = 5 To 13 Step 2
                If IsEmpty(.Cells(V, C)) Then Exit For
                R = R + 1
                Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
            Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L
            End If
        End With
    Next
        Application.EnableEvents = True
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

The mention tag failed , so I have to use this method

[Kelly mort]

The code worked like charm.

What I need now is an explanation to what's going on there so that I modify it in the future.

Most of the lines in your code are very strange to me. This means I have a long way to go. Lol.

[Kelly mort]

Hi @Marc L

I just found out that I am not getting the reply for the explanation because I am not asking the right questions.

So, my actual dataset is from column BZ to CT.

I am kind of working on some complex dataset ATM.

Column BZ contains ID while CA takes names as, the one with the prototype I produced.

So between the Target and owe, as on the prototype, I have three more columns between.

But the rest stay the same.

And instead of starting the data from row 2 as on the prototype, my actual data start from row 7.

I know I am asking too much, but I have no other option.

Can you please adjust the previous code to suit for me?

Regards.

[Marc L]

Can you please adjust the previous code to suit for me?

I won't waste time if you at least well follow the direction in my previous post
and my demonstration still does not work on your side with your original attachment !

As another point I work only on what I can load under Excel …

[Kelly mort]

I won't waste time if you at least well follow the direction in my previous post
and my demonstration still does not work on your side with your original attachment !

As another point I work only on what I can load under Excel …

Okay thanks.

But I will be glad if you can add some comments to the part of code I posted in post #6

In that case I will be able to adjust it myself.

Once again, I am very grateful

[Marc L]

Except Application.Match which is the MATCH worksheet function (so to see first in the Excel inner help if necessary)
all statements are yet in the VBA inner help : just place the text cursor on a statement, hit F1 key then read …
As that's the way I learned VBA.

Another tip to understand what each codeline does : add first a breakpoint (F9 key) in the worksheet module
at the beginning of the event, on sheet side change the B1 cell value,
the execution stops on the breakpoint codeline then you can watch out what happens on sheet side
or for any variable see its value within the VBE Locals window. Move to the next codeline via hitting F8 key (step-by-step mode) …

[Kelly mort]

That was very helpful.

I am so grateful for your time and patience.

I was able to make it adjusted

[Marc L]

Well done ‼ And thanks for the rep !

[Kelly mort]

Well done ‼ And thanks for the rep !

Please have a look at this link for me. Thanks
https://www.excelforum.com/excel-pro...heets-vba.html