Insert rows with copying formula from above row

Hi there!

This is going to be my first post and probably the first macros-enabled excel sheet created by me, so I hope for your help!

So I'm trying to do the following:

The sheet consists of 13 columns.
I want to protect it and then send to my colleagues. The main feature of it would be 2 buttons to insert the rows. The first button would insert all 13 columns below selected cell in a row and copy all formatting. Also cell g contains the formula which I'd like to be filled down (NOT COPIED, like I saw many times in the answers). So basically this is a recorded macro of what I'm trying to do:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
    Rows("72:72").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G71").Select
    Selection.AutoFill Destination:=Range("G71:G72"), Type:=xlFillDefault
    Range("G71:G72").Select
End Sub

The second button would do the following: it will insert the row below selected cell and merge cells from A to J with the cells from above rows (not all together, I need A72 to be merged with A 71, then B72 with B71, etc). Then two cells K and L would be just inserted and again cell M would be merged with above cell (like M2 with M1)
So below is the VBA code that I want to be done by just clicking the button:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Rows("71:71").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A71:A72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B71:B72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("C71:C72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("D71:D72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("E71:E72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("F71:F72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("G71:G72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("H71:H72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("I71:I72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("J71:J72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("M71:M72").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
End Sub

L&G, if somebody would help me with that I would probably be one of the happiest men in the world.

I'm also attaching the sheet so you understand what I want exactly.
sample.xlsm
And to make me happy for the rest of my life - can you tell me how can I hide all columns starting from AA to the end and rows starting from 50?

Hello masta-s,

And to make me happy for the rest of my life

To make the Forum Moderators happy, and before you can expect a reply to your problem, you will have to read and follow the Forum Rules. > Wrapping your Code <, for instance.

Hi masta-s,

Merging Cells causes a myrid of problems. Would it be o.k. with you if we did not Merge any Cells?

Hi, masta-s,

maybe try these two modified versions of your macros:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
If Selection.Cells(1).Row < 13 Then Exit Sub
 Rows(Selection.Cells(1).Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G" & Selection.Cells(1).Row - 1).AutoFill Destination:=Range("G" & Selection.Cells(1).Row - 1 & ":G" & Selection.Cells(1).Row), Type:=xlFillDefault
End Sub

I went from A to J on this, we could use an array to hold all Columns

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Dim lngCol As Long
    
    Rows(ActiveCell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    For lngCol = 1 To 10
      With Range(Cells(ActiveCell.Row, lngCol), Cells(ActiveCell.Row + 1))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Merge
      End With
    Next lngCol
    With Range("M71:M72")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Merge
    End With
End Sub

Ciao,
Holger

Hi masta-s,

Try the attached WorkBook.

Hi, Winon,

instead of unprotecting the worksheet prior to use of the macro and thereafter I tend to protect the Worksheet on Activation with the parameter UserInterfaceOnly:=True which allows macros to be effective on that sheet while normal actions canīt take place. Behind the Sheet:

Private Sub Worksheet_Activate()
ActiveSheet.Protect Password:="abc", UserInterfaceOnly:=True
End Sub

@masta-s:
I strongly agree with Winon about Merged Cells - I should know how to handle them but try to avoid them whereever possible (my alternative is Center Across if needed or rely on Word for better "painting" abilities). Iīm sorry I messed the worksheet up - I didnīt test the code but that wasnīt at all expected to happen.

Ciao,
Holger

@ HaHoBe,

I agree with the Password UserInterFace suggestion. I was just so caught up with everything else and the Merge Cells, that I have overlooked it.

However, I have changed your Merge code to Center Across Selection and it seems to be o.k., as long as the user remembers to select the Cell directly below the one to be "Stretched".

@ masta-s,

You can give this revised version a try as well.

Here you go then,

Also see the "Please consider" note at the bottom of this Post.