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