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

I may be able to answer my own question: I juts noticed that paste special
has a "skip blanks" box, Imagine I've never realized this in decades of
EXCEL!

D


"Dean" wrote in message
...
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

hey dean - did this work the way you intended?????
thanks
susan


On Feb 6, 11:50 am, "Dean" wrote:
I may be able to answer my own question: I juts noticed that paste special
has a "skip blanks" box, Imagine I've never realized this in decades of
EXCEL!

D

"Dean" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Guess you're one step ahead of me! No, it didn't. Also, I saw paste link
which is what I really want but, if you select it, it hushes the skip blanks
option, so I still get the zeroes problem. Help, please.

Thanks!
Dean


"Susan" wrote in message
oups.com...
hey dean - did this work the way you intended?????
thanks
susan


On Feb 6, 11:50 am, "Dean" wrote:
I may be able to answer my own question: I juts noticed that paste
special
has a "skip blanks" box, Imagine I've never realized this in decades of
EXCEL!

D

"Dean" wrote in message

...



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- Hide quoted text -

- Show quoted text -

=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

You can change your formula to:

=if(oldsheet!a1="","",oldsheet!a1)



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

--

Dave Peterson

which if checked disables paste links. If you don't want links, then it is
unclear why copying the sheet didn't do what you wanted.

--
Regards,
Tom Ogilvy


"Dean" wrote:

I may be able to answer my own question: I juts noticed that paste special
has a "skip blanks" box, Imagine I've never realized this in decades of
EXCEL!

D


"Dean" wrote in message
...
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

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

Actually, I forgot to mention that I did try that. I just want to avoid all
the if statements as the workbook is already huge and complex, although I do
still want this page live, i.e., I;m not willing to paste the new results
each time something in the workbook changes.

Looks like Tom has cooked up a macro based approach which, in this case,
will hopefully get around this problem. Normally, I prefer non-macro
approaches, since I';m not good at them, but this is an exception.

Thanks Dave,
Dean


"Dave Peterson" wrote in message
...
You can change your formula to:

=if(oldsheet!a1="","",oldsheet!a1)



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

--

Dave Peterson

Actually, I now realize you have the same statement above, so I should be
able to figure out the problem. Looks like the next statement goes on the
next line. Let me try that.
"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

Yup, that did it. Worked like a charm.

Thanks to everyone who helped!

Dean

"Dean" wrote in message
...
Actually, I now realize you have the same statement above, so I should be
able to figure out the problem. Looks like the next statement goes on the
next line. Let me try that.
"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

In the second loop, the "next" got wrapped to the previous line

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

should be

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

Just for completeness, I believe you figured it out.

--
regards,
Tom Ogilvy



"Dean" wrote in message
...
Yup, that did it. Worked like a charm.

Thanks to everyone who helped!

Dean

"Dean" wrote in message
...
Actually, I now realize you have the same statement above, so I should be
able to figure out the problem. Looks like the next statement goes on
the next line. Let me try that.
"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

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