Automating copy/paste/paste special when row references change
If I read his message correctly, Carl needed to paste twice - once for
formulas in a new row, and once to replace the "old" last row with values. So
amending the code slightly to do the second paste:
---------------------------------------------
Sub Macro2test()
'
' Macro2test Macro
' yada yada
'
' Keyboard Shortcut: Ctrl+f
'
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & Lastrow & ":W" & Lastrow).Copy
' Paste formulas into new row:
Range("B" & (Lastrow + 1)).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Replace existing row with values:
Range("B" & (Lastrow)).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Turn off the "highlight" around the copied cells:
Application.CutCopyMode = False
End Sub
---------------------------------------------
|