Insert rows with copying formula from above row

**Winon** · 10-07-2012, 03:23 AM

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.

**masta-s** · 10-07-2012, 04:34 AM

Originally Posted by Winon

Hello masta-s,

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.

Winnon, thanks for help, hope this will work now :-)

**Winon** · 10-07-2012, 05:02 AM

Hi masta-s,

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

**masta-s** · 10-07-2012, 05:21 AM

Originally Posted by Winon

Hi masta-s,

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

Winon,

Sure it will work. I just have an excel sheet where they've managed to apply that, but unfortunately it's password protected as well as VBA code is pw protected too. But it looks so nice...

Anyway, if there's a lot to do to make this work then sure at least pasting the rows without merging will do. Thanks in advance!

**HaHoBe** · 10-07-2012, 05:36 AM

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