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