Incorporate VBA and Copy and Paste

Question

I wonder whether someone may be able to help me please.

I'm using the code below to autopopulate a number of columns with pre-defined text when a value is entered into column "B".

Option Explicit
Public preValue As Variant


    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim cell As Range, res As Variant
        Dim FirstBlankCell As Range
        Dim lr As Long
        Dim msg
        Dim rCell As Range
        Dim Rng As Range, Dn As Range
        Dim Rng1 As Range
        Dim Rng2 As Range
        Dim Rng3 As Range
        Dim Rng4 As Range
        Dim Rw As Range


        If Target.Cells.Count > 1 Then Exit Sub

        lr = lr


        With Target
            Select Case True

            Case .Column = 2
            If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
                    .Offset(, 1).Value2 = "Yes"
                    .Offset(, 2).Value2 = "--Select--"
                    .Offset(, 3).Value2 = "--Select--"
                    .Offset(, 4).Value2 = "--Select--"
                    .Offset(, 5).Value2 = "Enter your FTE"
                    .Offset(, 6).Value2 = "C&R"
                    .Offset(, 7).Value2 = "--Select--"
                    .Offset(, 17).Value2 = "Enter the name of your Line Manager"
                  End If
            Case Else
            End Select
        End With

    End Sub

This works fine, but I'd now like to extend this functionality a little further. I'd also like the population of columns to occur if values are copied and paste into column "B" in addition to when they are entered manually.

This will help when I migrate the information from exisitng sheets into the new one I'm building. Although this is the last change I need to make, it's proving the hardest to find a solution for.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

Chris

Answer 1

As per Karen, the event does fire if contents are cut and pasted. The thing is, the code you posted checks to see if the number of cells returned is greater than one, and if so, it stops the event from executing:

If Target.Cells.Count > 1 Then Exit Sub

Change the code to this, and it should work just fine.

Dim cell As Range, res As Variant
Dim FirstBlankCell As Range
Dim lr As Long
Dim msg
Dim rCell As Range
Dim Rng As Range, Dn As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rw As Range

lr = lr

Dim r As Range
For Each r In Target.Cells
With r
    Select Case True

    Case .Column = 2
    If .Value2 > 0 And .Value2 <> "Enter your name" And .Offset(, -1) = "" Then
            .Offset(, 1).Value2 = "Yes"
            .Offset(, 2).Value2 = "--Select--"
            .Offset(, 3).Value2 = "--Select--"
            .Offset(, 4).Value2 = "--Select--"
            .Offset(, 5).Value2 = "Enter your FTE"
            .Offset(, 6).Value2 = "C&R"
            .Offset(, 7).Value2 = "--Select--"
            .Offset(, 17).Value2 = "Enter the name of your Line Manager"
          End If
    Case Else
        End Select
    End With
Next r

What was done here was that line of code was removed to allow the event to fire even if more cells that one are in the target range, and then we use a For Each loop on the cells contained in the Target to work on each row.

Nim

Answer 2

The Worksheet_Change(ByVal Target As Range) event is fired by both manual entry and pasting of data, so it should just work. Did you try it?