Home » Forums » Open Source Learning Center » Question and Answer

Modify Recorded Macro to Insert Rows in any Area

Submitted by nickelcell on 26 January, 2013 - 23:49
Hi Team. I've searched and searched for answers. I found some great answers but didn't seem to work for my case. I'm sure it's because I am very new to VBA. :-) I would like to find certain phrase in a row i.e. "TPF FUND V - SERIES J TOTALS" then insert a number blank rows just above it then copy the functions from the existing row above newly added row. The number of rows to enter will change at times. Two, three, sometimes 5 rows at a time. I recorded a macro which works fine except that the location to insert row(s) will frequently change. Can I modify the recorded code below to add row(s) in various areas of the worksheet? If so, what would I change? Listed below is the code from my recorded macro that will be assigned to a button: 
Sub Test_InsRow()
'
' Test_InsRow Macro
' Select tabs and insert row in same area across selected worksheets
'

'
    Sheets(Array("All_Fund_Series", "MAPMG", "SCPMG")).Select
    Sheets("All_Fund_Series").Activate
    Cells.Find(What:="TPF FUND V - SERIES J TOTALS", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Rows("213:213").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A212:W212").Select
    Selection.AutoFill Destination:=Range("A212:W213"), Type:=xlFillDefault
    Range("A212:W213").Select
    Range("D213:F213").Select
    Selection.ClearContents
    Range("R213").Select
    Selection.ClearContents
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("All_Fund_Series").Select
 
End Sub
Thanks in advance. Nickelcell
‹ Edit links Insert a row if column b has a blank line. ›
»