Dynamic Copy/Paste Special Formulas/Paste Special Values

Dynamic Copy/Paste Special Formulas/Paste Special Values

Post by Sharon Per » Sun, 08 Aug 2004 04:14:06


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

Post by Tom Ogilv » Sun, 08 Aug 2004 04:21:15

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

--
Regards,
Tom Ogilvy

 
 
 

Dynamic Copy/Paste Special Formulas/Paste Special Values

Post by JE McGimps » Sun, 08 Aug 2004 04:24:21

Take a look at David McRitchie's INSRTRow macro:


http://www.yqcomputer.com/


In article <18d301c47be9$8afb2dd0$ XXXX@XXXXX.COM >,
 
 
 

Dynamic Copy/Paste Special Formulas/Paste Special Values

Post by SidBor » Mon, 09 Aug 2004 05:49:36

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
Range(cells(rng.row,1),Cells(rng.row,256).End(xltoLeft))