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