excel - how to duplicate each row with a macro

Question

I have excel sheet with N+1 rows where Column A has unique id N.

I need to duplicate each row so that below a row N there will be three new rows with unique-ids N-b, N-c, N-d

e.g. sample input rows:

id1    data here 
id2    data2 here 

e.g. sample output:

id1    data here 
id1-b  data here 
id1-c data here
id1-d data here
id2    data2 here 
id2-b  data2 here 
id2-c data2 here
id2-d data2 here

Answer 1

If I need to know how to do something using VBA, the easiest way to find out is to do it manually and record my actions in a macro. It is then usually a simple procedure to edit the macro to my precise requirements.

Answer 2

You could try something like this

Sub Macro1()
Dim sheet As Worksheet
Dim usedRange As Range

    Set sheet = ActiveSheet
    Set usedRange = sheet.usedRange

Dim i As Integer

    For i = 1 To usedRange.Rows.Count
        Dim row As Range
        Set row = usedRange.Rows(((i - 1) * 4) + 1)

        Dim iCopy As Integer

        For iCopy = 1 To 3
            row.Copy
            Dim insertRow As Range
            Set insertRow = usedRange.Rows(((i - 1) * 4) + 1 + iCopy)
            insertRow.insert xlDown
            Dim copiedRow As Range
            Set copiedRow = usedRange.Rows(((i - 1) * 4) + 1 + iCopy)
            copiedRow.Cells(1, 1) = copiedRow.Cells(1, 1) & "-" & Chr(97 + iCopy)

        Next iCopy
    Next i
End Sub