Solved: How to copy only formats and formulas, not values

[RKramkowski]

I've got a table built in Excel and am looking to find the last row, and then copy a cell range in that row, and paste it one row below, but only paste formats and formulas, NOT values.

The following code finds the last used row in column B but then copies everything, including values into the next row.


Sub NewIssue()
lastrow = Sheets("Issues").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Issues").Range("B" & lastrow & ":" & "P" & lastrow).Copy
Sheets("Issues").Range("B" & lastrow + 1 & ":" & "P" & lastrow + 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

I've tried all sorts of things with PasteSpecial but everything copies values. I even recorded a macro manually stepping through the copy and paste but noticed that even a manual "paste special" copies values as well. I inherited this spreadsheet and never worked with a table inside a spreadsheet. What am I missing when working with a table?

Thanks

[Bob Phillips]

This works for me




Dim LastRow As Long

With Sheets("Issues")

LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("B" & LastRow).Resize(, 15).Copy
.Range("B" & LastRow + 1).Resize(, 15).PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("B" & LastRow + 1).Resize(, 15).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub

[RKramkowski]

Hi, yours seems to do the same as mine. It copies everything, including values. I remember years ago doing this and it worked fine, except that there was no table involved. The only thing I can attribute the difference to is that this is a table embedded in Excel. I have no idea why xlPasteFormats seems to paste all (not just formats) in a table.

So for now, I did it by brute force. After copying the row, I do a ClearContents on those cells that do not have a formula associated with them.

I'd rather have a more elegant way of doing it, but this works for now.

[Bob Phillips]

No it doesn't, it copies constants if they are constants, formulae if they are formulae. It doesn't copy values.