If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy cell format to cell on another worksht and update automatical
I have two worksheets, I have entered the formula for a cell on the 2nd
worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#2
|
|||
|
|||
i think you will need a simple macro to do this ie paste
special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#3
|
|||
|
|||
Hi Paul,
thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#4
|
|||
|
|||
Kevin
When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#5
|
|||
|
|||
Hi Gord,
unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#6
|
|||
|
|||
Gord,
I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#7
|
|||
|
|||
It could be that the active cell is not in row 10 or below, but in row 1-9.
For example, if you are in cell B3, an offset of -9 rows is not possible. On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#8
|
|||
|
|||
Kevin
Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#9
|
|||
|
|||
Gord/Myrna,
thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
#10
|
|||
|
|||
Hi experts,
thanks to your help I am having 'some' success with macros but I am finding their behaviour somewhat unpredictable. Rather then debug this one problem at a time and have to keep pestering you for help each time I thought I should give you the full picture of what I am trying to accomplish here. Maybe what I am trying to do is beyond the capabilities of Excel, if so I would like to know so that I don't need to keep hassling you for help with something which at the end of the day Excel might not be able to handle. Ultimately I want my spreadsheet to consist of two worksheets, sheet1 would contain column D which corresponds to a list of pins for a computer chip I am working on. Sheet2 would be an array of rows and columns representing the physical view of the computer chip which I am working on. This would essentially be a square array of pins. Each of these pins would be numbered, i.e. A1, A2,etc, much like an array of rows and columns appears on a spreadsheet. Column D on sheet1 would contain a list of values, pointing to a cell on sheet2 .. Example1: D2, value = C17 D3, value = D17 D4, value = C20 etc, etc (in total there would be 448 rows of D to be parsed (D2449)) Each of these D cells would be colored by the user according to their electrical function. What I want to do is read the value of each D cell and use it's contents (e.g. 'C17') as a pointer to the corresponding cell on sheet2, (i.e. C17, D17, etc) then I want to do a copy/paste special to copy the format info (i.e. color) to the cells on sheet2. I want to do this for all D cells within the range D2449. The pin array would be 26x32, this would be represented on sheet2 as 26 rows by 32 columns. I want this to all be automated, which I assume will require some huge macro to be created. could you please advise if Excel would be capable of doing this? thanks, Kevin "kevinm" wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps Paul "kevinm" wrote: I have two worksheets, I have entered the formula for a cell on the 2nd worksheet so that the cell contents are copied from a cell on the first worksheet. e.g. =(Sheet1!B1) When I update the contents of the cell on the first worksheet the contents of the cell on the 2nd sheet are updated automatically .. so far so good .. Now, what I really want to do is have the format of the cell on the first sheet (and NOT it's contents) copied to the cell on the 2nd worksheet. When I change the format of the cell on the 1st worksheet I want the cell on the 2nd sheet to update automatically. The reason for needing this behaviour is as follows: Sheet 1 is used to represent a collection of signal pins of a computer chip I am working on. These signals can be grouped together according to similar function and I color format these according their function. (there are about 600 pins which are grouped by function and represented by about ten different colors). Sheet2 is a physical view of the computer chip. I want it to be able to reassign signals on the first sheet and have Excel automatically update the color format of the cells on the second sheet. This way I can quickly see how changing the pin assignment is reflected in the physical view of the chip. e.g. Sheet 1, A1 is a signal named 'A', it is formatted BLUE Sheet 2, AA10 shows the format of cell A1 on sheet1, i.e. BLUE Suppose I decide to change A1 on sheet1 to a different signal (B), but this signal is of type RED. What I want to see is Excel automatically update AA10 on sheet2 so that its format is RED. Is it possible to do this in Excel? thanks, Kevin |
Thread Tools | |
Display Modes | |
|
|