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 "=")?

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 "=")?

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 "=")?

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?

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 "=")?

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 "=")?

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 "=")?