locked
Insert Rows and Copy Formulas from above from Button RRS feed

  • Question

  • We have worksheets that are protected and users need a way to insert rows and copy formulas from above down.

    Our protection uses interface only and allows inserting rows, but we need a way to allow them to copy the formulas from a button on the worksheet.

    I have code that inserts and copies from the row above where the insert starts, but this copies all the values (data and formulas)

    I only want to copy formulas.

    This is what I have so far:

    Sub InsertRowCopy()
    Dim StartRow As Long
    Dim EndRow As Long
    StartRow = Selection.Rows(1).Row
    EndRow = Selection.Rows.Count + StartRow - 1
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(StartRow - 1 & ":" & StartRow - 1).Select
    Selection.Copy
    Rows(StartRow & ":" & EndRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End Sub

    Wednesday, June 20, 2018 3:35 AM

Answers

  • One additional row at the bottom of the code.

    Sub InsertRowCopy()
        Dim StartRow As Long
        Dim EndRow As Long
        StartRow = Selection.Rows(1).Row
        EndRow = Selection.Rows.Count + StartRow - 1
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows(StartRow - 1 & ":" & StartRow - 1).Select
        Selection.Copy
        Rows(StartRow & ":" & EndRow).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Rows(StartRow & ":" & EndRow).SpecialCells(xlCellTypeConstants).ClearContents
    End Sub


    Regards, OssieMac

    • Marked as answer by James N San Wednesday, June 20, 2018 11:21 AM
    Wednesday, June 20, 2018 6:17 AM

All replies

  • One additional row at the bottom of the code.

    Sub InsertRowCopy()
        Dim StartRow As Long
        Dim EndRow As Long
        StartRow = Selection.Rows(1).Row
        EndRow = Selection.Rows.Count + StartRow - 1
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows(StartRow - 1 & ":" & StartRow - 1).Select
        Selection.Copy
        Rows(StartRow & ":" & EndRow).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Rows(StartRow & ":" & EndRow).SpecialCells(xlCellTypeConstants).ClearContents
    End Sub


    Regards, OssieMac

    • Marked as answer by James N San Wednesday, June 20, 2018 11:21 AM
    Wednesday, June 20, 2018 6:17 AM
  • Works great. Thanks!
    Wednesday, June 20, 2018 11:21 AM