Copy from one worksheet to another, aggregate from many lines to one based on value in column

[Hoppbananen]

Hi!

So I have this problem I'm stuck on...

I currently have a set of data that's being filled in partly automatic and partly manually, that when user is finished with I'd like to copy to another worksheet and add some information based on the value in column E of the original sheet. Please review my code under:

Sub CreateImportsheet()
    Dim ows As Worksheet
    Dim tws As Worksheet
    Dim c As Range
    Dim i As Long

    Set ows = Sheets("Data") 'Original sheet
    Set tws = Sheets("Import") 'Target sheet
    i = 3 'this is the first row in the target sheet
    
    With ows
        For Each c In .Range("E2:E412").Cells
              If c.Value = "A" Then
                tws.Range(tws.Cells(i, "A"), tws.Cells(i, "F")).Value = .Range(.Cells(c.Row, "A"), .Cells(c.Row, "F")).Value
                tws.Cells(i, "G") = "6050"
                tws.Cells(i, "H") = tws.Cells(1, 2)
                tws.Cells(i, "I") = tws.Cells(1, 4)
                i = i + 1
              ElseIf c.Value = "B" Then
                tws.Range(tws.Cells(i, "A"), tws.Cells(i, "F")).Value = .Range(.Cells(c.Row, "A"), .Cells(c.Row, "F")).Value
                tws.Cells(i, "G") = "4050"
                tws.Cells(i, "H") = tws.Cells(1, 2)
                tws.Cells(i, "I") = tws.Cells(1, 4)
                i = i + 1
              End If
        Next c
    End With

End Sub

This works fine! However now I have to rebuild it to fit in another format, gonna try and explain with some pictures first:

This is a dummy of my original table:

ID_Name	ID_Nr	Postal code	Some info_1	Value_X	Some info_2
	1​	1000​		A
	1​	1001​		A
	1​	1002​		A
	2​	1003​		B
	1​	1004​		A
	3​	1005​		B
	3​	1006​		B
	4​	1007​		A
	4​	1008​		A
	4​	1009​		A
	4​	1010​		A

And I want to go trough it and create this:

ID_Name	ID_Nr	Postal code_From	Postal code_To	Some info_1	Value_X	Some info_2	Adding info_1	Adding info_2	Adding info_3
	1​	1000​	1002​		A
	2​	1003​	1003​		B
	1​	1004​	1004​		A
	3​	1005​	1006​		B
	4​	1007​	1010​		A

Where I essentially go through ID_Nr and create an interval of postal codes if value in ID_Nr isn't broken. First three lines have the same ID_nr, therefore the data exported to new table will be on one line with the same ID_Nr and MIN postal code and MAX postal code (or first or last if you want). Column containing postal codes in original sheet will always be sorted from smallest to largest. I then add info based on the value in Value_X, in addition to some user added input (through a cell reference).


Is there anybody out there that can lead me in the right direction?

BR
-Hoppbananen

 

[Hoppbananen]

No one?