Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Paste Formulas Only
How can you copy a range and then paste cell contents only if the cell
contents is a formula (cell contents begins with "=")? |
#2
|
|||
|
|||
Pick up only the formulas
select the range to copy, then do Edit=Goto =Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" wrote in message ... How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? |
#3
|
|||
|
|||
Isn't it so that it then will only paste the values?
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Tom Ogilvy" wrote in message ... Pick up only the formulas select the range to copy, then do Edit=Goto =Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" wrote in message ... How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? |
#4
|
|||
|
|||
No. With PasteSpecial you can say to paste formulas.
On Fri, 25 Mar 2005 22:14:23 +0100, "Niek Otten" wrote: Isn't it so that it then will only paste the values? |
#5
|
|||
|
|||
Thanks, but I don't think this will work for me. I'm trying to create a macro
that will insert a new row at the bottom of a table, then copy and paste only the formats and formulas from the previous row (if there are any there) and not paste any text or numerical data. The problem I found with the GoTo method is that if formulas are found in non-adjacent cells and you copy and then try to paste formulas, they will be pasted to adjacent cells. Also, if you use GoTo in a macro and there are no formulas found, you will get a runtime error. "Tom Ogilvy" wrote: Pick up only the formulas select the range to copy, then do Edit=Goto =Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" wrote in message ... How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? |
#6
|
|||
|
|||
rows(i-1).copy Destination:=Cells(i,1)
on Error Resume Next set rng = rows(i).SpecialCells(xlConstants) On Error goto 0 if not rng is nothing then rng.ClearContents End if -- Regards, Tom Ogilvy "Brett" wrote in message ... Thanks, but I don't think this will work for me. I'm trying to create a macro that will insert a new row at the bottom of a table, then copy and paste only the formats and formulas from the previous row (if there are any there) and not paste any text or numerical data. The problem I found with the GoTo method is that if formulas are found in non-adjacent cells and you copy and then try to paste formulas, they will be pasted to adjacent cells. Also, if you use GoTo in a macro and there are no formulas found, you will get a runtime error. "Tom Ogilvy" wrote: Pick up only the formulas select the range to copy, then do Edit=Goto =Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" wrote in message ... How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? |
#7
|
|||
|
|||
Tom, first of all I want to thank you very much for your help so far. Your
solution almost works, but I was having some problems with it. Im not very good with VB so I wasnt able to get around the error caused by i. I guess the value of i needs to be defined as the active cells row number some how, but I wasnt getting anywhere with the Help files on how to do this. Instead, I tried the following: ActiveCell.EntireRow.Copy ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate Selection.EntireRow.Insert Shift:=xlDown Application.CutCopyMode = False On Error Resume Next Set rng = ActiveCell.EntireRow.SpecialCells(xlConstants) On Error GoTo 0 If Not rng Is Nothing Then rng.ClearContents End If This works great, unless the row copied has nothing but blank cells and cells containing formulas. In that case, the macro returns Run-time error 424: Object required for the line If Not rng Is Nothing Then. One more tweak will do the trick, Im sure. Thanks for your help, Brett "Tom Ogilvy" wrote: rows(i-1).copy Destination:=Cells(i,1) on Error Resume Next set rng = rows(i).SpecialCells(xlConstants) On Error goto 0 if not rng is nothing then rng.ClearContents End if -- Regards, Tom Ogilvy "Brett" wrote in message ... Thanks, but I don't think this will work for me. I'm trying to create a macro that will insert a new row at the bottom of a table, then copy and paste only the formats and formulas from the previous row (if there are any there) and not paste any text or numerical data. The problem I found with the GoTo method is that if formulas are found in non-adjacent cells and you copy and then try to paste formulas, they will be pasted to adjacent cells. Also, if you use GoTo in a macro and there are no formulas found, you will get a runtime error. "Tom Ogilvy" wrote: Pick up only the formulas select the range to copy, then do Edit=Goto =Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" wrote in message ... How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop formulas from incrementing when you copy and paste? | Excel Worksheet Functions | |||
Copy & Paste Visible Cells with Formulas | Excel Worksheet Functions | |||
copy and paste formulas | Excel Worksheet Functions | |||
Cut and Paste into spreadsheet with locked formulas | Excel Worksheet Functions | |||
How to cut and paste with locked formulas | Excel Discussion (Misc queries) |