VBA Paste formats and formulas only

[Rachey]

Please help, my code keeps pasting values. The rows im copying have values in some cells and formulas in others, i only want the formatting and the formulas this is driving me crazy, this is what i have. Is there a way to copy the formulas and leave blank the cells with just values in?
Sub NewRows()

Dim Last_Row As Long
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row + 2

Rows("24:28").Copy
Cells(Last_Row, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Cells(Last_Row, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End sub

 

[BiocideJ]

This will copy and paste only the cells in rows 24-28 that contain formulas in their cells.

Sub NewRows()
 
Dim Last_Row As Long
Dim FormulaRange As Range
Dim CopyRange As Range
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row + 2
Set FormulaRange = Rows("24:28").SpecialCells(xlCellTypeFormulas)
For Each CopyRange In FormulaRange.Areas
    CopyRange.Copy
    With Cells(Last_Row + CopyRange.Row - 24, CopyRange.Column)
        .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
   Application.CutCopyMode = False
Next CopyRange
Set FormulaRange = Nothing
Set CopyRange = Nothing
End Sub

The reason you were copying values was because you were copying EVERYTHING from 24:28 and then pasting the formulas into the destination range, however, the 'formula' of a static value cell is just the value and so it writes that value as the formula when pasting.

 

[Rachey]

Thank you, Ive realised (only because its worked ) That actually I do need a value from the copied range. Is there a way to use code to paste formulas and then clear contents from certain columns for example paste everything then clear contents between C:D and F:G?

Also your code didnt find the 'new' last row when running the code after the first time, it pasted over the same rows over and over. Thank you for helping ive been trying to do this for 2 days now my vba knowledge isnt great.

 

[BiocideJ]

I just used your code for finding the last row. If it is failing I suspect it is because you have values in column A (which are not being pasted) and causing it to keep finding row 28 (or wherever the last cell with a value/formula is in column A) over and over again.

I haven't tested the following, but if you are simply trying to remove what goes into columns (C:D,F:G) you should just be able to do the following to your original code.

Sub NewRows()
Dim Last_Row As Long
 Last_Row = Cells(Rows.Count, 1).End(xlUp).Row + 2
 
Rows("24:28").Copy
 Cells(Last_Row, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Cells(Last_Row, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
 Range(Cells(Last_Row, "C"), Cells(Last_Row + 4, "D")).ClearContents
 Range(Cells(Last_Row, "F"), Cells(Last_Row + 4, "G")).ClearContents
End Sub

 

[Rachey]

you are amazing thank you so much works perfectly