Solved: Incorporate VBA With Copy and Paste

[hobbiton73]

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

I'm using the attached file to record staff resource, but I have a problem when copying and pasting data into the sheet as opposed to manually entering the information.

If you enter your name in column "B", you'll see that there are a number of columns which are now populated by pre-defined text.

However, the problem I have is that when I copy and paste the names into column "B", these columns are no longer populated with this text.

I'm not particularly well versed in VBA, and depsite trawling through a number of tutorials, I've been unable to find a solution whereby the VBA text populates the cells, both when information is manually entered into the sheet and through copy and paste.

I just wondered whether someone may be able to take a look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cll As Range, CellsToProcess 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
On Error GoTo EndNow
Set CellsToProcess = Intersect(Columns(2), Target)
'CellsToProcess.Select 'debug.
If Not CellsToProcess Is Nothing Then
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.EnableCancelKey = xlDisabled
Sheets("Input").Protect "handsoff", UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
For Each cll In CellsToProcess.Cells
' Target.Cells.Count > 1 Then Exit Sub
With cll
' Select Case True
' Case .Column = 2 'it has to be
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
' End Select
End With
Next cll
Columns("A:S").EntireColumn.AutoFit
End If
EndNow:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.EnableCancelKey = xlInterrupt
End Sub
?

[hobbiton73]

Hi @p45cal, thank you so very much for taking the time to read and reply to my post.

Your solution works perfectly!

Once again many thanks for your time and trouble.

Kind regards

Chris