VBA Add row in table if new combination appears

I am a bit struggling with a way to variably adding lines to table according to capex category, partner number and period.

Mu final table looks like (more sections, but for example), it is not a listobject item:

my source looks like (more pivot tables, but for example):

I am indexing data from source to final table. But when combination do not exist, like the orange field in data source, in final table it should be Capex O - 3to5 years, and partner 1115, I don't know how to add it.

I thought of find function in case I would create unique ID for every line (combination of Capex letter, position (period ID) and partner), but in this case I am a bit stuck with different treatment of periods in final table and source. In final table 1-2 years and 2-3 years are combined as well as 3-4 years and 4-5 years. And I don't think transposing the source will be useful (you can prove me wrong).

Also I would need add lines in final table only for situations where value is <> 0.

I would need to add the new row in A-Z order for every capex category and period (except if its 999999, which should always be in first position; and 999999 is default value, so it is already in every capex category and period)

Every green column has its own calculated workbook where source is listed on separate sheet (Pivot)

Hope I described it clear, in any doubts, please let me know.

EDIT 18 Mar 22:

So, I almost figured it out, except couple issues.

First I created support columns in pivot sheet using concatenate on partner ID and position (there is such concat in original table, not presented before)

My code (wsSUM is declared in Public section of the code, as it is used accross modules; also you don't see whole code, only part regarding this issue):

Dim wbname As String
Dim wsp As Worksheet, wspsqa As Worksheet
Dim lcol As Long, lrow As Long
Dim i As Integer, n As Integer, x As Integer, r As Integer, RowCount As Integer, FR As Integer, LR As Integer, z As Integer, t As Integer
Dim catID As Variant
Dim sInt As Range



        Rows("3:3").Find(What:="Total to IKOS").Select
        Range(Selection, Selection.End(xlDown)).Select
    i = Selection.Rows.Count
.
.
.

 z = 0 'row number in pivot - wsp
    n = 0 'column number in pivot - wsp
    t = 0 'row number of left,9 for catID - wsSUM
    'i is declared above, rows count in wsSUM for current quarter

    For z = 2 To lrow
        For n = 5 To 8
            If wsp.Cells(z, lcol   n).Value <> "" Then
            catID = wsp.Cells(z, lcol   n).Value
                'sInt = CInt(wsSUM.Range("D4:D" & i))
                If wsSUM.Range("A4:A" & i).Find(What:=catID) Is Nothing Then
                    t = wsSUM.Range("D4:D" & i).Find(What:=Left(catID, 9)).Row
                    'Debug.Print Left(catID, 9)
'declare row number
                        wsSUM.Rows(t   1 & ":" & t   1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                        wsSUM.Range("D" & t   1).FillDown
                        wsSUM.Range("P" & t   1).FillDown
                        wsSUM.Range("A" & t   1).FillDown
                        With wsSUM.Range("E" & t   1)
                            .FormulaR1C1 = wsp.Cells(z, 2).Value
                            .NumberFormat = "@"
                        End With
                        
'get row number for period merged cells
                            RowCount = wsSUM.Range("C" & t).MergeArea.Rows.Count
                            FR = wsSUM.Range("C" & t).MergeArea.Row
                            LR = wsSUM.Range("C" & t).MergeArea.Row   wsSUM.Range("C" & t).MergeArea.Rows.Count - 1
    
'sort data
                        With wsSUM.Sort
                            .SortFields.Add2 Key:=Range("E" & FR   1 & ":E" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                            .SetRange Range("E" & FR   1 & ":Q" & LR)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
                        
                        i = i   1 'with every added line is needed to broaden count
'                    Debug.Print "added to row " & FR & "; value is " & wsp.Cells(z, 2).Value & " " & catID
'                    Debug.Print "A4:A" & i
                    
                End If
            End If
        Next n
    Next z

issue 1 - better declaration of i - I have one block of data (current quarter) starting on row 3 (header), after the first block, there are couple blank lines and another block (previous Q); I would need only rows count for current block of data, I am not able to do it without selection

issue 2 - Partner IDs in final table are stored as text, in Pivot as number, so I have an issue as some IDs are starting with 0 and find part of the code won't find them; this is only issue for Partner IDs starting with 0; I tried CInt, but was not able to apply this on Range

sInt = CInt(wsSUM.Range("D4:D" & i))

issue 3 - given the fact, I need 999999 on the first place and I am sorting the other data in A-Z, I don't know how to stretch merged cells in case data are added in that manner, the merged cell won't merge automatically

CodePudding user response：

The code above is working, issue 2 will be asked in separate thread and thread will be posted here

issue 1 is working - even though I would prefer without select, it is working...

issue 3 - possibly same as issue 2 if I won't find any better solution, in that case I will post here the solution for issue 3 too

Edit: thread for issue 2 Find number in text with leading zero