Fill a sheet with data from different sheets same workbook in a very tricky way - Vol 2

Status
Not open for further replies.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Cross posted at:

Reason:
To place my problem before more experts

.......................................................................................
.......................................................................................
This post is an upgrade for the request at:
https://www.excelforum.com/excel-pro...ricky-way.html

of which @Marc L provided a great solution for a while ago.

Link to sample workbook:

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 - 2 '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
I have added additional sheet with the name "EXTRA_DATA". And what this sheet does is recording extra entries in case the 5 slots on any of the sheets, "GROUP " 1 to 3 are exhausted.

And for that matter, the column, "GROUP" on the "EXTRA_DATA" sheet keeps track of the various groups as shown on the sample data sheet.
Then the "YEAR" column also records the particular year that record is linked to (There may be multiple years).

So, with the two columns added, I am looking for a way that I can get the data (from "EXTRA_DATA" sheet merged or attached to the right or corresponding group on the output sheet as shown on the "OUT_PUT_DATA" sheet (NEW REQUEST).

I have been staring at the script for a while now and the only meaningful thing I was able to do was to change :


Code:
 For N = 1 To Me.Index - 1
to
Code:
 For N = 1 To Me.Index - 2

Which of course produced a result (which didn't get me to smile either).

Could someone please help me out?

Thanks in advance
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Status
Not open for further replies.

Forum statistics

Threads
1,213,585
Messages
6,114,518
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top