Event drivin copy and paste code

[GoBucks]

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]

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

 

[Dave Peterson]

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

 

[slarbie]

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

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]

What happens if more than one cell is changed?

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

 

[GoBucks]

Dave & Slarbie..Thank you for your help. This is perfect! Both worked just
the way I hoped.

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

 

[slarbie]

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!

What happens if more than one cell is changed?

 

[GoBucks]

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!!