Insert and xlDown messed up formatting

[mysticmario]

Ok
here's the thing
I have something like this:

When i press second button from the left it executes this code:

Private Sub NewPage_Click()

    Range("A2:P57").Select
    Selection.Copy
    Range("A2:D2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

it simply takes this template and put new one above this.
Unfortunately when the "old" template gets moved down it messes up the entire cell formattting

I have no idea how to fix this. If I delete newly created template everything moves up and formattign is back to normal.
Please help

 

[GWteB]

It seems you're having merged cells. Perhaps it's better to take all columns into account, like in:

Private Sub NewPage_Click()

    Range("A2:P57").Copy
    Range("A2:P2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[mysticmario]

It seems you're having merged cells. Perhaps it's better to take all columns into account, like in:

Private Sub NewPage_Click()

    Range("A2:P57").Copy
    Range("A2:P2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Didnt help. It seems the issue is only with height property.
In the other tab all cells have the same height and i have the same new page function implemented, it works like a charm

 

[mysticmario]

Still didnt fix that eh...

 

[mysticmario]

I fix that
The issue was in the line where the column is very streteched in tha last cell fo the row I had leftover data which wasnt showing due to cell size but it was inside.
After that I also made all the cells autosize themselves so there wasn't any otehr issues with formatting.
From that point everything works great.
Thanks for your assitance

 

[GWteB]

Glad you worked it out and thanks for the follow-up.

In case you are still interested in a solution other than using Excel's AutoFit (which sometimes doesn't have the effect one would expect, especially when the Wrap Text feature is enabled...) you can use one of the two macros below.

The first macro (and its dependents) does the same as your post #1 code and can be used if the columns to the right of your worksheet form contain data or shapes that you do not want to shift down. Because Excel is database oriented (where the rows act as records) it actually makes sense that with a partial insertion like this only the data shifts downwards and the row heights do not. From this design might also be explained, that row heights cannot be pasted while column widths can be pasted (using PasteSpecial). Therefore when wanted we have to copy row heights ourselves. In the code below this is done through a separate procedure. After the partial insertion only the height of the rows at the bottom of the data section need to be adjusted each time. A separate function is used to determine the location of that data section.

Sub mysticmario_v1()

    Dim FirstPage As Range, NewFirstPage As Range, LastPage As Range

    Application.ScreenUpdating = False

    Set FirstPage = Range("A2:P57")     ' <<< change range address to suit
    With FirstPage

        .Insert Shift:=xlDown           ' note that after insertion FirstPage's address has been changed!
        Set NewFirstPage = .Offset(-.Rows.Count)
        Set LastPage = GetLastPage(NewFirstPage)
        .Copy NewFirstPage
        CopyRowHeights NewFirstPage, LastPage

    End With
    Application.ScreenUpdating = True
End Sub


Function GetLastPage(ByVal argFirstPage As Range) As Range
    Dim PageCount As Long
    If Not argFirstPage Is Nothing Then
        With argFirstPage
            PageCount = VBA.Int(.Parent.UsedRange.Rows.Count / .Rows.Count) - 1
            If PageCount > 0 Then
                Set GetLastPage = .Offset(.Rows.Count * PageCount)
            End If
        End With
    End If
End Function


Sub CopyRowHeights(ByVal argSource As Range, ByVal argTarget As Range)
    Dim i As Long
    If Not argSource Is Nothing And Not argTarget Is Nothing Then
        With argSource
            For i = 1 To .Rows.Count
                argTarget.Rows(i).RowHeight = .Rows(i).RowHeight
            Next i
        End With
    End If
End Sub

The second macro however inserts entire rows (and thereby records are respected). That way row heights don't need te be adjusted at all, so a few lines of code will do. This macro can be used in case the columns to the right of your worksheet form don't contain data or shapes.

Sub mysticmario_v2()
    With Range("2:57")         ' <<< change start and ending row to suit
        .Insert Shift:=xlDown
        .Copy .Offset(-.Rows.Count)
    End With
End Sub

Hope this helps.

 

[mysticmario]

That is quite the solution aswell, well beyond my current knowledge, but i somewhat get it.