Dynamic Copy/Paste Special Formulas/Paste Special Values
 

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!

Dynamic Copy/Paste Special Formulas/Paste Special Values
 

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(xlto Left))
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

--
Regards,
Tom Ogilvy

"Sharon Perez" wrote in message
...
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!

Dynamic Copy/Paste Special Formulas/Paste Special Values
 

Take a look at David McRitchie's INSRTRow macro:


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


In article ,
"Sharon Perez" wrote:

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!

Dynamic Copy/Paste Special Formulas/Paste Special Values
 

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
-----Original Message-----
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(xlto Left))
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

--
Regards,
Tom Ogilvy

"Sharon Perez" wrote in message
...
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!


.