VBA code for pasting formulas also pastes values

[Trille]

Hi!

I have written a code that creates new lines above a given section and should copy the formatting and formulas from the row above. However, the line for copying formulas also carries over values which I would like to avoid.. The sub looks as follows:

Private Sub CommandButton1_Click()
Dim c As Long
For c = 100 To 1 Step -1
If Cells(c + 1, 3) Like "Next section" Then
Cells(c + 1, 1).EntireRow.Insert
Rows(c).Copy
Rows(c).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Rows(c).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End If
Next c
End Sub

Does anyone know how I should amend the sub to clear the values? Alternatively, I'd like to delimit the copying to columns K, L and O.

Any help is greatly appreciated!

/Tristan

 

[Anthony47]

Alternatively, I'd like to delimit the copying to columns K, L and O.

I guess this version should do what you asked:

Private Sub CommandButton1_Click()
Dim c As Long, CCopy, J As Long
CCopy = Array("K", "L", "O")            '<<< the columns you wish to copy
For c = 100 To 1 Step -1
    If Cells(c + 1, 3) Like "Next section" Then
        Cells(c + 1, 1).EntireRow.Insert
        For J = 0 To UBound(CCopy)
            Cells(c, CCopy(J)).Copy Cells(c + 1, CCopy(J))
        Next J
        Application.CutCopyMode = False
    End If
Next c
End Sub

Try...

 

[Fluff]

How about

Private Sub CommandButton1_Click()
Dim c As Long
For c = 100 To 1 Step -1
   If Cells(c + 1, 3) Like "Next section" Then
      Cells(c + 1, 1).EntireRow.Insert
      Rows(c).Resize(2).FillDown
      On Error Resume Next
      Rows(c + 1).SpecialCells(xlConstants).ClearContents
      On Error GoTo 0
   End If
Next c
End Sub

 

[Trille]

Hi Anthony,

Works well, thank you so much! I just added the command for copying the entire row -> pasting the cell formatting.

Again, I really appreciate your help!

 

[Trille]

Hi Fluff,

That one also worked well! I really appreciate your help guys, and seeing different methods for solving the problem definitely boosts the learning curve.

Thanks a lot!

 

[Fluff]

Glad we could help & thanks for the feedback.