Prev: Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
Next: Sort issue Sort Of
From: DA on 11 Apr 2010 18:24
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

From: DA on 12 Apr 2010 02:16
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
From: Project Mangler on 12 Apr 2010 03:28
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" <deanalt(a)aol.com> wrote in message
news:b24b0d07-2e9a-4f18-85b4-0137b58a49f8(a)c36g2000yqm.googlegroups.com...
> 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
>


From: DA on 12 Apr 2010 04:39
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



From: Project Mangler on 12 Apr 2010 16:14
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!






 | 
Pages: 1
Prev: Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
Next: Sort issue Sort Of