Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
I have a dataset on worksheet (L6:BL155). I am looking for a way in that when
a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
So if you make a change to L6, then nothing special happens. K isn't part of
the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
ps. I have a typo in the original code.
Change this: For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell to For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value End With '<-------------Added Application.EnableEvents = True Next myCell Dave Peterson wrote: So if you make a change to L6, then nothing special happens. K isn't part of the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
Here's more or less the same idea, but without the looping
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Long, r As Long, isect As Range Set isect = Intersect(Target, Range("L6:BL155")) If isect Is Nothing Then Exit Sub c = Target.Column r = Target.Row Application.EnableEvents = False Range(Cells(r, 12), Cells(r, c - 1)).Value = Target.Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: ps. I have a typo in the original code. Change this: For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell to For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value End With '<-------------Added Application.EnableEvents = True Next myCell Dave Peterson wrote: So if you make a change to L6, then nothing special happens. K isn't part of the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
What happens if more than one cell is changed?
slarbie wrote: Here's more or less the same idea, but without the looping Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Long, r As Long, isect As Range Set isect = Intersect(Target, Range("L6:BL155")) If isect Is Nothing Then Exit Sub c = Target.Column r = Target.Row Application.EnableEvents = False Range(Cells(r, 12), Cells(r, c - 1)).Value = Target.Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: ps. I have a typo in the original code. Change this: For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell to For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value End With '<-------------Added Application.EnableEvents = True Next myCell Dave Peterson wrote: So if you make a change to L6, then nothing special happens. K isn't part of the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
Dave & Slarbie..Thank you for your help. This is perfect! Both worked just
the way I hoped. "slarbie" wrote: Here's more or less the same idea, but without the looping Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Long, r As Long, isect As Range Set isect = Intersect(Target, Range("L6:BL155")) If isect Is Nothing Then Exit Sub c = Target.Column r = Target.Row Application.EnableEvents = False Range(Cells(r, 12), Cells(r, c - 1)).Value = Target.Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: ps. I have a typo in the original code. Change this: For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell to For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value End With '<-------------Added Application.EnableEvents = True Next myCell Dave Peterson wrote: So if you make a change to L6, then nothing special happens. K isn't part of the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
Well, I was going from his description of the scenario "user inputs a value
in a cell", which would only result in one cell change at a time. But you make a good point - clearly it's smarter to deal with a broader range of possibilities than the problem called for. So thanks for prompting me to rethink it! :) "Dave Peterson" wrote: What happens if more than one cell is changed? slarbie wrote: Here's more or less the same idea, but without the looping Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Long, r As Long, isect As Range Set isect = Intersect(Target, Range("L6:BL155")) If isect Is Nothing Then Exit Sub c = Target.Column r = Target.Row Application.EnableEvents = False Range(Cells(r, 12), Cells(r, c - 1)).Value = Target.Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: ps. I have a typo in the original code. Change this: For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell to For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value End With '<-------------Added Application.EnableEvents = True Next myCell Dave Peterson wrote: So if you make a change to L6, then nothing special happens. K isn't part of the range to fix or to look for changes in. If that's true, you could use a worksheet event. If you want to try, rightclick on the worksheet tab that should have this behavior and select view code. Then paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myIntersect As Range Dim myCell As Range 'Just check for a change in M:BL 'since there would be no cells to the left if 'the change were made in L Set myRng = Me.Range("M6:BL155") Set myIntersect = Intersect(Target, myRng) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells Application.EnableEvents = False With Me .Range(.Cells(myCell.Row, myRng.Column - 1), _ .Cells(myCell.Row, myCell.Column - 1)).Value _ = myCell.Value Application.EnableEvents = True Next myCell End Sub GoBucks wrote: I have a dataset on worksheet (L6:BL155). I am looking for a way in that when a user inputs a value in a cell, a macro will then copy and paste (as values) that same value to the cells left of it in that row. For example, if the value of 100 is input in cell P9, then 100 will then be automatically populated from L9 to O9. If the value 50 is input in Z13, then 50 will be populated from L13 to Y13, etc. Was looking for pasting as values due to conditional formatting formulas in the range and don't want to effect those. Also, it doesn't matter if there is a value present in a cell to the left of cell being input. I would have it override that value. Is this possible? Thank you! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event drivin copy and paste code
slarbie...After doing some testing, I found one issue with the code and was
wondering how to fix. The code works great unless whenever a value is input in Column L, it updates the cell to the left in Column K. Any other time a value is placed in Columns M through BL, it updates only back to L. Is this correctable?? Thx!! "slarbie" wrote: slarbie wrote: Here's more or less the same idea, but without the looping Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Long, r As Long, isect As Range Set isect = Intersect(Target, Range("L6:BL155")) If isect Is Nothing Then Exit Sub c = Target.Column r = Target.Row Application.EnableEvents = False Range(Cells(r, 12), Cells(r, c - 1)).Value = Target.Value Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
event macros vs copy/paste | Excel Worksheet Functions | |||
Ws Selection Change Event Code, Copy a Cell problem | Excel Programming | |||
Copy picture and remove event code | Excel Programming | |||
Change event copy & paste | Excel Programming | |||
Worksheet_Change Event - Macro kills copy and paste | Excel Programming |