Paste Formulas Only

B

Brett

How can you copy a range and then paste cell contents only if the cell
contents is a formula (cell contents begins with "=")?
 
T

Tom Ogilvy

Pick up only the formulas

select the range to copy, then do Edit=>Goto =>Special and select formulas

then do your copy.
 
N

Niek Otten

Isn't it so that it then will only paste the values?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
B

Brett

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.
 
T

Tom Ogilvy

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
 
B

Brett

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. I’m not very
good with VB so I wasn’t able to get around the error caused by “iâ€. I guess
the value of “i†needs to be defined as the active cell’s row number some
how, but I wasn’t 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, I’m sure.

Thanks for your help,
Brett
 
Top