How To create Macro that copy cells, paste them in the first available row and clear some content

Question

I'm trying to create a VBA Macro that copies a Range of cells, paste them in the same Sheet in the first available row and then clear the content of the copied cells, leaving the formulas.

So far I've come up with the Copy/Paste but not the clear content.

Range("A1:L5").Copy
Range("A1").End(xlDown).Offset(6).EntireRow.Insert
Range("B2:B4").ClearContents

and then clear the content of the copied cells, **leaving the formulas** Can you clarify this part? — Siddharth Rout
leaving the formulas, with this I mean that I would like to clear the content of the cells that I've inserted, but leaving the calculated formulas. I.e: In one column the user manually insert some values and the other columns are filled with calculated formulas based on the inserted values. I would like to clear the manually inserted valued but leaving the formulas, so when the user insert new values the other cells are automatically filled up — Francesca Manicardi

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-12-17T11:16:05

Copy everything to the destination, then use .SpecialCells(xlCellTypeConstants).Clear to clear all constant values in the destination.

Option Explicit

Public Sub CopyFormattingsAndFormulasOnly()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet

    Dim Source As Range
    Set Source = ws.Range("A1:L5")

    Dim Destination As Range
    Set Destination = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(RowOffset:=1).Resize(Source.Rows.Count, Source.Columns.Count)

    Source.Copy

    Destination.PasteSpecial xlPasteAll
    On Error Resume Next
    Destination.SpecialCells(xlCellTypeConstants).ClearContents
    On Error Goto 0
End Sub

How To create Macro that copy cells, paste them in the first available row and clear some content

1 Answers