Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet2 = sheet1
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet2 = sheet1
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet2 = sheet1
DA,
Play around with this: Sheets("Sheet1").Copy Befo=Sheets(2) Application.DisplayAlerts = False Sheets("Sheet2").Delete Application.DisplayAlerts = True Worksheets(2).Name = "Sheet2" "DA" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet2 = sheet1
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet2 = sheet1
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
go to sheet1 to sheet2 | Excel Programming |