That is why I suggested this with the extra step to remove those If
statements for blank cells:

or use this

=IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1)

then select all cells on the new sheet and do
Edit=Goto=Special and select formulas and errors. Then do Edit=Clear
Contents

Of course I emphasize selecting all cells (select the gray button in the
upper left corner at the intersection of the row and column headers)
--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I'm amazed that you churn such an answer out for the macro so quickly!

I tried the very clever EXCEL approach and it is really interesting
because I've never used this kind of functionality. Still, it leaves me
with IF statements in all the non empty cells that I'd rather not have,
since the workbook is already a monster. When I copied in the macro
approach, the two rows before the end if statement turned red font. Is
there some special way to copy macros from posts so that it doesn't stick
in extra characters (perhaps when there is a carriage return) or did you
perhaps, type in an extra spacebar or something like that?

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

or use this

=IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1)

then select all cells on the new sheet and do
Edit=Goto=Special and select formulas and errors. Then do Edit=Clear
Contents

That will work if the original sheet didn't have any formulas showing
error
values.

for a macro

Make the new sheet the active sheet and run this code.


Sub CopyData()
Dim rng as Range
Dim rng1 as Range
Dim cell as Range
On Error Resume Next
with worksheets("Sheet1")
set rng = .cells.Specialcells(xlconstants)
set rng1 = .cells.SpecialCells(xlFormulas)
End with
On Error goto 0
if not rng is nothing then
for each cell in rng
activesheet.Range(cell.address).formula = "=" & _
cell.Address(0,0,xlA1,True)
next
end if
if not rng1 is nothing then
for each cell in rng1
activesheet.Range(cell.address).formula = "=" & _
cell.Address(0,0,xlA1,True) next
end if
End Sub

--
Regards,
Tom Ogilvy


"Dean" wrote:

I need to create a new worksheet in my workbook which is largely a copy
of
an existing one. So, I made a copy of the worksheet. Then, in cell A1,
I
set it equal to the old worksheet's cell A1. That worked fine, but
since
there are lots of empty spaces in between important rows and columns in
the
old worksheet, I'd like the new one to have empty spaces there too.
When I
simply copy the equation =oldsheetA1 from cell A1 of the new sheet,
across
and down, I get a bunch of zeroes where the old sheet is blank, and I
want
blanks in the new sheet too. Unfortunately, there are many blanks and
they
appear almost randomly so it would be very time consuming to simply go
to
each new empty cell and delete it. Also, sometimes the zeroes are a
result
of a computation, not merely an indication that the old worksheet's cell
was
a blank.

I was thinking I could use some sort of isempty function but I'm not
sure
there is such a thing, anyway I'd rather not have RAM wasted on such a
thing. I'd be happier to do the copying from cell A1 to the rest of
the
worksheet with a macro. The macro should simply set newsheet equal to
oldsheet whenever old sheet has something in it. If not, it should
leave it
blank. If it helps, the worksheet has only 250 rows but almost the
entire
maximum range of columns.

Thanks
Dean