sheet2 = sheet1

[DA]

I want to create a sheet2 and, between cells A1 and P200, any time
that sheet1 has something in one of those cells, whatever it might be,
I want sheet2 to simply reference that result. If sheet1 is empty in
that cell, then I want sheet2 to also be the same.

It seems that the easiest way to do this would be to make a copy of
sheet 1, name that sheet2, then have a macro that replaces its
contents with =sheet1!A1, if we are tlaking about cell A1, for
example, and so on and so forth.

I suppose I could simply set every cell in that block eqaul to it, but
I fear that I will have a bunch of zeroes showing up on sheet2 in
places where sheet1 is blank. Please keep in mind that empty cells in
sheet1 may have all sorts of unknown formats in them, so I can't just
assume that if a certain cell is blank in sheet1 that it will remain
balnk in sheet2, if I just paste sheet1 formats onto sheet2, but the
cell format happened to be, say, "currency". The blank would show up
as 0.00.

Yes, I am open to other, more clever, ways to do this, but it seems
like a macro as described above would do the trick.

Can someone kindlty help me with such a macro?

Thanks
Dean

 

[Lee Li Fong]

Dean,

Try this formula to all cells A1200 of Sheet2. If there is empty cell in Sheet1, nothing show in Sheet2, else equal to the cell.
=IF(Sheet1!A1="","",Sheet1!A1)
Hope can help you.

Lee Li Fong
http://smartusemicrosoftoffice.blogspot.com/

 

[DA]

The problem with that approach is a bunch of my cells are left aligned
or right aligned and contain strings of texts that are wider than what
will fit in the cell. But, since the cells next to them were empty,
the whole text would show. With your approach, it doesn't show.

That said, there were only about 50 such cells being cutofff on sheet2
and they were reasonably noticeable, so I could simply delete the
equations from the 'empty' adjacent cells that were getting in the
way. So, bottom-line, I think this is good enough. I thank you.

It would still be best if someone could provide the macro. Anyone?

Thanks
Dean

 

[Project Mangler]

DA,

Play around with this:

Sheets("Sheet1").Copy Before:=Sheets(2)
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
Worksheets(2).Name = "Sheet2"

 

[DA]

This is nice, too. But, if I change sheet1, I need to remember to
rerurn the macro to make sure sheet2 will change, which is not ideal.
Also, there will be a few cells from sheet1 that I will not want to
show on sheet 2.

I actually wanted the macro to initially set up the new sheet, ay
which point I would edit out a few cells from sheet 2 and, perhaps,
format a few diffferently. I think I'm there now, so I think this is
all good enough.

Thanks to both of you!
Dean

 

[Project Mangler]

DA,

Glad you found a solution.

I can't resist replying to the points you make:

This is nice, too. But, if I change sheet1, I need to remember to
rerurn the macro to make sure sheet2 will change, which is not ideal.

If you put the code in the code page of sheet1 by using the following event:
Private Sub Worksheet_Change(ByVal Target As Range)

Then the macro will run when sheet1 changes

Also, there will be a few cells from sheet1 that I will not want to
show on sheet 2.
I actually wanted the macro to initially set up the new sheet, ay
which point I would edit out a few cells from sheet 2 and, perhaps,
format a few diffferently. I think I'm there now, so I think this is
all good enough.

You could set up code to do all of this and more on selected ranges of
cells.

There, my conscience is clear!