EXCEL MACRO TO REPEAT AND INSERT ROWS [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I am trying to write a macro that will insert rows as follows:
Col 1 Col2 Col3
1 S 456
2 A 222
3 M 952

So i want the macro to copy and insert as follows (number continues to add, while the other columns repeat in the same order as a group).

Col 1 Col2 Col3
1 S 456
2 A 222
3 M 952
4 S 456
5 A 222
6 M 952

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
This is based on assumption that
1. Data to be copied start from row 2
2. The series is in column A
3. Row 1 has the max used column

Sub RepeatAndRinse()

Dim lMaxRows As Long
Dim iMaxCols As Long
Dim iStartAtRow As Integer

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    iMaxCols = Cells(1, Columns.Count).End(xlToLeft).Column
    iStartAtRow = 2
    
    Range(Cells(iStartAtRow, 2), Cells(lMaxRows, iMaxCols)).Copy
    Cells(lMaxRows + 1, 2).Select
    ActiveSheet.Paste
    
    Cells(lMaxRows, 1).Select
    Selection.AutoFill Destination:=Range("A" & lMaxRows & ":A" & lMaxRows * 2 + (1 - iStartAtRow)), Type:=xlFillSeries
   
End Sub