Private Sub Worksheet_Change(ByVal Target As Range)
 

Have the following code in the referenced module:

If Not (Intersect(Target, Range("A3:A65536")) Is Nothing) Then
Range("a:a").EntireColumn.AutoFit
ActiveSheet.Protect Password:="xxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
ActiveSheet.EnableSelection = xlNoRestrictions
End If

The autofit works fine. But when I copy and paste data into Column A, it
only allows me to paste the data once; if I want to paste the same data into
another part of Column A, I have to go back and copy the data again and then
paste it. It is as if it is clearing the clipboard (which it is not), or
maybe it has to do with the protection (all cells in Column A are unlocked).
Am at my wits end trying to figure out what I'm doing wrong. Can someone
please advise?

Private Sub Worksheet_Change(ByVal Target As Range)
 

You could try adding

Target.copy

at the end of your code so that you could copy it again.

"Paige" wrote:

Have the following code in the referenced module:

If Not (Intersect(Target, Range("A3:A65536")) Is Nothing) Then
Range("a:a").EntireColumn.AutoFit
ActiveSheet.Protect Password:="xxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
ActiveSheet.EnableSelection = xlNoRestrictions
End If

The autofit works fine. But when I copy and paste data into Column A, it
only allows me to paste the data once; if I want to paste the same data into
another part of Column A, I have to go back and copy the data again and then
paste it. It is as if it is clearing the clipboard (which it is not), or
maybe it has to do with the protection (all cells in Column A are unlocked).
Am at my wits end trying to figure out what I'm doing wrong. Can someone
please advise?