Excel VBA Macro - Insert copied range of cells in between every row of selection in excel

Question

I need some help on my homework. I have 2 worksheets. The first worksheet looks something like this

Second worksheet looks something like this.

I need to copy from the first worksheet and insert to the second worksheet. Output looks something like this:

I am currently stuck now. This is my current codes:

Sub insertabc(LastRow As Long)
MsgBox LastRow

For i = 1 To LastRow
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next i
          
End Sub

Answer 1

A few things you should pay attention to:

• When iterating with a for-loop, you should (normally) use it to go through a range/array/set, i.e. Range("A" & i)

• You should fully qualify which range you're talking about including the sheet, i.e. Dim rngInsert As Range: Set rngInsert = ThisWorkbook.Worksheets("First")

• Using variables is easier to use down the line, i.e. Dim rng As Range : Set rng = Selection (but don't forget next point)

• Selection is almost never necessary so try to avoid as much as possible, this will prevent issues and increase performance: Dim rng As Range: Set rng = ws2.Range("A2:A" & LastRow) with ws2: Dim ws2 As Worksheet: Set ws2 = ThisWorkboook.Worksheets("Second") (Second is just the name of the worksheet)

• When adding/deleting rows, work backwards so the iteration works as intended.

For you that could look like this:

Sub copyValuesInBetween()
    Dim ws1 As WorkSheet, ws2 As WorkSheet
    Dim i As Long, lRow As Long
    
    Set ws1 = ThisWorkbook.Worksheets("First")
    Set ws2 = ThisWorkbook.Worksheets("Second")
    lRow = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    
    For i = lRow To 2 Step -1 'backwards looping
        ws2.Range("A" & i).Offset(1).EntireRow.Insert
        ws2.Range("A" & i).Offset(1,1).Resize(,3).Value = ws1.Range("A" & i-1).Resize(,3).Value 
        'give values instead of copying
        'i-1 for ws1 since you have them starting at Row1 and in sheet2 at Row2
    Next i
End Sub

Note that this won't check for selection or copy from sheet1 but it will get the result you showed :)