copy only non-empty cells

[Dean]

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

 

[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

 

[Susan]

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

 

[Dean]

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

 

[Tom Ogilvy]

=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

 

[Dave Peterson]

You can change your formula to:

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

 

[Tom Ogilvy]

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.

 

[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

 

[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

 

[Dean]

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]

Yup, that did it. Worked like a charm.

Thanks to everyone who helped!

Dean

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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)