Copy rows of data to another sheet in Excel and add column VBA

Question

I can't get my head around this.

I have a data structure like this:

I would like to end up with something like this (in another sheet).

If column E is equivalent to 2, then the row should be copied to the other sheet, and the row with the same ID (Column A) the name in that row should be inserted in the final row.

Actually, I'm trying to merge/combine 2 rows, as seen in the picture, such that each ID number is only represented once in Sheet2.

Sub Test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim i As Integer
    Dim last_row As Integer
    last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
    For i = 2 To last_row
        If sh.Range("D" & i).Value = 2 Then
            sh.Range("A1:G3").Copy _
            Worksheets("sheet2").Range("A1")
        End If
    Next i
End Sub

For now, I can only copy a range. And then I'm confused by which order to do the subtasks.

Yes, there will be either only 1 unique ID or at most 2 identical ID. — User123456789
– User123456789, Commented Dec 15, 2020 at 16:57
Actually it should also be there, thank you for noticing. However, it's just a matter of repeating the same procedure as inserting name2. — User123456789
– User123456789, Commented Dec 15, 2020 at 17:47

Warcupine · Accepted Answer · 2020-12-15 17:00:18Z

1

This is using a dictionary to make sure you don't copy multiples of the same id.

I'm assuming based on your example you always want the first instance of each id.

    Dim sourcesh As Worksheet
    Dim destsh As Worksheet
    
    Set sourcesh = ThisWorkbook.Sheets("Sheet1")
    Set destsh = ThisWorkbook.Sheets("Sheet2")
    
    Dim i As Long
    Dim j As Long
    Dim lr As Long
    
    With sourcesh
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        j = 1
        For i = 2 To lr
            If .Cells(i, 4).Value = 2 Then
                If Not dict.exists(.Cells(i, 1).Value) Then
                    destsh.Cells(j, 1) = .Cells(i, 1)
                    destsh.Cells(j, 2) = .Cells(i, 2)
                    destsh.Cells(j, 4) = .Cells(i, 3)
                    destsh.Cells(j, 5) = .Cells(i, 4)
                    destsh.Cells(j, 6) = .Cells(i, 5)
                    destsh.Cells(j, 7) = .Cells(i, 6)
                    destsh.Cells(j, 8) = .Cells(i, 7)
                    
                    dict.Add .Cells(i, 1).Value, j
                    j = j + 1
                Else
                    destsh.Cells(dict(.Cells(i, 1).Value), 3) = .Cells(i, 2)
                End If
            End If
        Next i
    End With

edited Dec 15, 2020 at 17:00

answered Dec 15, 2020 at 16:31

Warcupine

4,7203 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

User123456789 Over a year ago

Nice, this is indeed the first step. Then I have to figure out how to insert the other name in a separate column.

User123456789 Over a year ago

I will indeed take starting point in this!

Warcupine Over a year ago

Whoops, missed that in the question, the edit should address that.

User123456789 Over a year ago

Thank you Warcupine, you saved me from a lot of headaches and frustrations.

Collectives™ on Stack Overflow

Copy rows of data to another sheet in Excel and add column VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related