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!

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

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

Take a look at David McRitchie's INSRTRow macro:

http://www.yqcomputer.com/

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

http://www.yqcomputer.com/

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

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))

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))

1. Paste Special, Formulas, is also pasting values. Why?

2. Where can I find the old "Paste Special" (Edit/Paste Special)?

3. Copy/Paste or Copy/Paste Special of filtered view to access

4. excel 2007 paste special can't paste formula

5. My "paste special - formula" in Excel pasts values and formula?

6. Text truncated when pasted into Excel (Paste Special - Paste Link)

7. paste special pastes into hidden cells while paste does not in 200

8. Insert vs Copy-Paste vs Paste Special etc.

9. Automating copy/paste/paste special when row references change

10. help w/ generic copy & paste/paste special routine

11. Copy & paste graphics -- only "paste special"?

12. Paste validation, Copy, Paste Special Validation

13. Copy; Paste; Paste Special are disabled

14. Word 2000 - Change Copy-Paste Special paste default from HTML to Unformatted text?

15. pasting a forumla w/o the paste/special/value

4 post • Page:**1** of **1**