Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my paste special function won't let me copy formulas | Excel Worksheet Functions | |||
Changing Of Values After A Copy And Paste Special: Why ? | Excel Discussion (Misc queries) | |||
Copy Values only without Paste Special | Excel Discussion (Misc queries) | |||
Copy / Paste Special / Values for a whole spreadsheet ? | Excel Discussion (Misc queries) | |||
How to copy formulas to a new workbook? Paste Special is different | Excel Worksheet Functions |