I get it, this is a super common gotcha with the Worksheet_Change event! The issue here is that when you paste multiple values at once, the Target parameter isn't a single cell—it's a range of cells. Your original code uses Target.Row, which only returns the row number of the first cell in that range, hence only the first row's column 37 gets the "S".

Here's how to fix it, with two approaches depending on your needs:

Solution 1: Loop Through Each Affected Cell (Most Reliable) #

This method works even if you paste into non-contiguous ranges (like selecting separate cells and pasting):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Integer
    Dim affectedRange As Range
    Dim cell As Range
    
    ' Disable events first to prevent infinite loops (since we'll modify cells here)
    Application.EnableEvents = False
    
    ' Get the last used row in column B of Sheet1
    lr = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    
    ' Define the range we care about: B2 to AJ[last row]
    Set affectedRange = Intersect(Target, Range("B2:AJ" & lr))
    
    If Not affectedRange Is Nothing Then
        ' Loop through every cell in the affected range
        For Each cell In affectedRange
            ' Assign "S" to column 37 (AK) of the same row
            Sheets("Sheet1").Cells(cell.Row, 37).Value = "S"
        Next cell
    End If
    
    ' Re-enable events so future changes trigger the macro
    Application.EnableEvents = True
End Sub

Solution 2: Bulk Assign Values (Faster for Large Ranges) #

If you only ever paste into contiguous ranges, this is more efficient—no loop needed:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Integer
    Dim affectedRange As Range
    
    Application.EnableEvents = False
    
    lr = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    Set affectedRange = Intersect(Target, Range("B2:AJ" & lr))
    
    If Not affectedRange Is Nothing Then
        ' Target all rows in column 37 that match the affected range's rows
        Sheets("Sheet1").Range("AK" & affectedRange.Row & ":AK" & affectedRange.Row + affectedRange.Rows.Count - 1).Value = "S"
    End If
    
    Application.EnableEvents = True
End Sub

Key Notes: #

• Application.EnableEvents = False: This is critical! When we modify column 37, it would trigger the Worksheet_Change event again, causing an infinite loop. Disabling events before making changes and re-enabling them afterward stops this.
• Intersect: We still use this to make sure we only process changes within your desired range (B2:AJ[last row]).
• Handling Non-Contiguous Ranges: The loop method works for any selection, while the bulk method only works if your pasted range is a single block of cells.

内容的提问来源于stack exchange，提问作者user7905187