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
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 :)