VBA & Macro duplicating rows

[HarryHarry]

Hello guys,

I'm new to macros and I need something for work which has been doing my head in, I can't figure it out.

I have a sheet with 1500 entries/rows. The column S contains frequency for each entry (monthly, quarterly, weekly...).

I need a macro that will check the column S and if it sees 'Monthly' then it will duplicate the row 11 times to get 12 in total, if quarterly then duplicate it 3 times to get 4 and so on for each row.

Furthermore I need to add a column next to the column S to indicate the number of the month per each row as well

Would anyone be kind enough to help me before I go insane?

Thanks a lot!

 

[mse330]

Hi Harry,

Try below code ...

Sub test()

Dim Lc&, r&

With ActiveSheet.[A1].CurrentRegion
   Lc = .Columns.Count + 1
   For x = .Rows.Count To 1 Step -1
      Select Case LCase(.Cells(x, "S"))
         Case "monthly": r = 11
         Case "quarterly": r = 3
      End Select
      If r > 0 Then
         .Rows(x).Offset(1).Resize(r, Lc).Insert
         .Rows(x).Offset(1).Resize(r, Lc) = .Rows(x).Value
         .Parent.Cells(x, Lc).Resize(r + 1, 1) = Evaluate("row(1:" & r + 1 & ")")
         r = 0
      End If
   Next
End With

End Sub