RKramkowski
05-18-2009, 11:16 AM
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
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