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

Here we go:

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.

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

Please Login or Register  to view this content.

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.

Originally Posted by 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 :

PHP Code: 

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

Originally Posted by Kelly mort

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 …

Originally Posted by Marc L

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

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) …

That was very helpful.

I am so grateful for your time and patience.

I was able to make it adjusted

Well done ‼ And thanks for the rep !

Originally Posted by Marc L

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