Dynamic Copy/Paste Special Formulas/Paste Special Values

[Sharon Perez]

How would someone copy the last row of data on a sheet, go
down one row, paste only the formulas, then go to the
original row and paste the values. The original row would
have the same values but no formulas and the next row
would then have the formulas. This needs to be dynamic
because the rows would increase by one row daily and the
reference would change each day. Thanks!

 

[Tom Ogilvy]

Assume the formulas should not adjust:

Sub ProcessLastRow()
Dim rng as Range, rng1 as Range
Dim cell as Range
set rng = Cells(rows.count,1).end(xlup)
set rng1 = Range(cells(rng.row,1),Cells(rng.row,256).End(xltoLeft))
rng1.copy
rng1.offset(1,0).pastespecial xlFormats
for each cell in rng1
if cell.hasformula then
cell.offset(1,0).Formula = cell.formula
cell.formula = cell.Value
end if
Next
End Sub

 

[JE McGimpsey]

Take a look at David McRitchie's INSRTRow macro:


http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[SidBord]

Here's the basic sequence I use a lot:
Assumming you have just selected the cell to be copied, then:
Selection.Copy
ActiveCell.Offset(1,0).Select
ActiveSheet.Paste '(Copy formula)
ActiveCell.Offset(-1,0).Select
Selection.PasteSpecial Paste=xlValues '(Remove formula)
NOTE: This sequence will NOT remove conditional formatting
To remove conditional formatting use:
Selection.FormatConditions.Delete