Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
Here's a tricky one:
I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
Maybe this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ps.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
On Apr 12, 5:10 pm, "PCLIVE" wrote:
Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ps.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
On Apr 12, 5:33 pm, "Shonzi" wrote:
On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
Try moving the following line:
Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message oups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
On Apr 13, 9:27 am, "PCLIVE" wrote:
Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message oups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
Ok. Try this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Paste Else End If End If enditall: End Sub "Shonzi" wrote in message ups.com... On Apr 13, 9:27 am, "PCLIVE" wrote: Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message oups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
On Apr 13, 4:19 pm, "PCLIVE" wrote:
Ok. Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Paste Else End If End If enditall: End Sub "Shonzi" wrote in message ups.com... On Apr 13, 9:27 am, "PCLIVE" wrote: Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message groups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this?- Hide quoted text - - Show quoted text - The code looks as if it should work, but it is not pasting anything in the next row. It's as if this is working: Range("C" & n & ":E" & n).Copy but this is not: Range("C" & (n + 1)).Paste I can see the cells that are to be copied get highlighted... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
This is a little closer to what you want...I think.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & (n)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Else End If End If enditall: Application.Calculate End Sub "Shonzi" wrote in message oups.com... On Apr 13, 4:19 pm, "PCLIVE" wrote: Ok. Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Paste Else End If End If enditall: End Sub "Shonzi" wrote in message ups.com... On Apr 13, 9:27 am, "PCLIVE" wrote: Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message groups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this?- Hide quoted text - - Show quoted text - The code looks as if it should work, but it is not pasting anything in the next row. It's as if this is working: Range("C" & n & ":E" & n).Copy but this is not: Range("C" & (n + 1)).Paste I can see the cells that are to be copied get highlighted... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
This one automatically selects cell in column B next to the cell in column A
that you just modified. Because of the cut and paste procedure, I'm not sure how to make the active cell be determined whether you press Enter or Tab. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & (n)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Range("B" & n).Select Else End If End If enditall: End Sub "PCLIVE" wrote in message ... This is a little closer to what you want...I think. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & (n)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Else End If End If enditall: Application.Calculate End Sub "Shonzi" wrote in message oups.com... On Apr 13, 4:19 pm, "PCLIVE" wrote: Ok. Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Paste Else End If End If enditall: End Sub "Shonzi" wrote in message ups.com... On Apr 13, 9:27 am, "PCLIVE" wrote: Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message groups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this?- Hide quoted text - - Show quoted text - The code looks as if it should work, but it is not pasting anything in the next row. It's as if this is working: Range("C" & n & ":E" & n).Copy but this is not: Range("C" & (n + 1)).Paste I can see the cells that are to be copied get highlighted... |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate the contents of a row when text is entered in a cell
On Apr 13, 5:46 pm, "PCLIVE" wrote:
This one automatically selects cell in column B next to the cell in column A that you just modified. Because of the cut and paste procedure, I'm not sure how to make the active cell be determined whether you press Enter or Tab. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & (n)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Range("B" & n).Select Else End If End If enditall: End Sub "PCLIVE" wrote in message ... This is a little closer to what you want...I think. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & (n)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Else End If End If enditall: Application.Calculate End Sub "Shonzi" wrote in message roups.com... On Apr 13, 4:19 pm, "PCLIVE" wrote: Ok. Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Paste Else End If End If enditall: End Sub "Shonzi" wrote in message groups.com... On Apr 13, 9:27 am, "PCLIVE" wrote: Try moving the following line: Range("A" & n + 1).Select Move this above 'Else'. "Shonzi" wrote in message groups.com... On Apr 12, 5:33 pm, "Shonzi" wrote: On Apr 12, 5:10 pm, "PCLIVE" wrote: Maybe this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n - 1) & ":E" & (n - 1)).Copy Range("C" & n).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub HTH, Paul "Shonzi" wrote in message ups.com... Here's a tricky one: I would like an action to occur each time text is entered into a cell in Column A. This needs to be done using VB, not a macro. When data is entered into, say, cell A2, I would like the formulas in cells C2:E2 to be copied down into cells C3:E3. The purpose of this is to avoid having formulas in cells until they are needed (i.e. each time a row with formulas is used, a new blank row with formulas is created). In doing a web search for a solution, I found the following code (props to Gord Dibben, circa 2003): Copy/paste this Event code to your worksheet module. Select Sheet TabView Code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("C" & n).Value = Excel.Range("B" & n).Value End If End If enditall: End Sub Unfortunately, I am unable to adapt this to my specific needs...anybody care to take a swing?- Hide quoted text - - Show quoted text - That was close, but I had to modify it slightly... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Else End If End If Range("A" & n + 1).Select enditall: End Sub Thank you! Couldn't have done it alone... :-)- Hide quoted text - - Show quoted text - So, with the above code I am getting two undesirable results: 1. The macro window appears when you open the sheet (I was hoping to avoid this by using VB, but it appears there is no way around that...) 2. Every time you change a cell that is NOT in Column A, the focus returns to cell A1. For example, if I am in cell B200 and I make a change, I am thrown rather abruptly up to A1 upon hitting enter or tab. It is less jarring with the windows frozen, so that Row 1 is alway on screen, but it is a nuisance nonetheless... Anyone have a solution to either of these issues??- Hide quoted text - - Show quoted text - That did the trick! Nice job, thanks again. So now the code looks like this... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & n & ":E" & n).Copy Range("C" & (n + 1)).Select ActiveSheet.Paste Range("A" & n + 1).Select Else End If End If enditall: End Sub There is still one problem, albeit a minor one. When entering data in a cell in Column A, if you hit tab (to go to Column B) it bumps you down to the next row instead, in Column A. Is there any way to fix this?- Hide quoted text - - Show quoted text - The code looks as if it should work, but it is not pasting anything in the next row. It's as if this is working: Range("C" & n & ":E" & n).Copy but this is not: Range("C" & (n + 1)).Paste I can see the cells that are to be copied get highlighted... That one was perfect! (Just one minor adjustment...) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then n = Target.Row If Range("A" & n).Value < "" _ Then Range("C" & (n) & ":E" & (n)).Copy Range("C" & (n + 1)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Range("B" & n).Select Else End If End If enditall: End Sub Thank you so much for all of your help! :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
preventing cell contents from being entered twice? | Excel Discussion (Misc queries) | |||
Cell contents don't display everything entered? | Excel Discussion (Misc queries) | |||
Formatted Cell Changes Format When Text Is Entered | Setting up and Configuration of Excel | |||
Text shown up in other cells everytime a text is entered in 1 cell | Excel Discussion (Misc queries) | |||
Entered text value equals a number I specify in another cell | Excel Worksheet Functions |