(Solved) – Keep cell value when activating macro


I have two columns where each cell from D11:D500 Adds a new rows with formula and formats
and E11:E500 delete rows.

however when i add new fields the ” ” and “-” is not copied to created rows.
How could I get this

Example image

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Change function to activate sub
    End Sub

    Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
       Dim x As Long
       If vRows = 0 Then
        vRows = Application.InputBox(Prompt:= _
          "Please specifiy how many new project lines you wan't to add", Title:="New project line", _
          Default:=1, Type:=1)
        If vRows = False Then Exit Sub
       End If

       Dim sht As Worksheet, shts() As String, i As Long
       ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       i = 0
       For Each sht In _
        i = i   1
        shts(i) = sht.Name

        x = Sheets(sht.Name).UsedRange.Rows.Count

        Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
         Resize(rowsize:=vRows).Insert Shift:=xlDown

        Selection.AutoFill Selection.Resize( _
         rowsize:=vRows   1), xlFillDefault

        On Error Resume Next
        Selection.Offset(1).Resize(vRows).EntireRow. _
       Next sht
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *