Looking to speed up and shorten my VBA Vlookup/find code

[Celticfc]

Hi all,

I'm new to VBA and found some codes online which I tried to combine together.I'm trying to move managers who leave the business from "manager 1" CH:CP to "leavers" page CH:CP, by doing a Vlookup to convert their details into variable and paste them to next blanks in leavers page. I feel I'm over repeating my codes and this leads to 1 minute of processing time so I would appreciate any suggestions to speed it up and cut down the repetitions.

Private Sub CommandButton1_Click()
  y = Sheets("one-pager profile").Range("e11").Value
viewmanagers = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 81, False)
managercomments = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 82, False)
oldstore = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 83, False)
newstore = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 84, False)
spoints = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 85, False)
assessment = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 86, False)
oldpos = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 87, False)
newpos = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 88, False)
newroledate = Application.WorksheetFunction.VLookup(y, Sheets("MANAGER 1").Range("F2:CZ2000"), 89, False)
Sheets("leavers").Range("ch" & Rows.Count).End(xlUp).Offset(1, 0) = viewmanagers
Sheets("leavers").Range("ci" & Rows.Count).End(xlUp).Offset(1, 0) = managercomments
Sheets("leavers").Range("cj" & Rows.Count).End(xlUp).Offset(1, 0) = oldstore
Sheets("leavers").Range("ck" & Rows.Count).End(xlUp).Offset(1, 0) = newstore
Sheets("leavers").Range("cl" & Rows.Count).End(xlUp).Offset(1, 0) = spoints
Sheets("leavers").Range("cm" & Rows.Count).End(xlUp).Offset(1, 0) = assessment
Sheets("leavers").Range("cn" & Rows.Count).End(xlUp).Offset(1, 0) = oldpos
Sheets("leavers").Range("co" & Rows.Count).End(xlUp).Offset(1, 0) = newpos
Sheets("leavers").Range("cp" & Rows.Count).End(xlUp).Offset(1, 0) = newroledate
Sheets("leavers").Range("ca" & Rows.Count).End(xlUp).Offset(1, 0) = "leaver"
Sheets("leavers").Range("cb" & Rows.Count).End(xlUp).Offset(1, 0) = UserForm5.TextBox29.Value ' leaver date
Sheets("leavers").Range("cc" & Rows.Count).End(xlUp).Offset(1, 0) = UserForm5.TextBox1.Value ' leaver comments
Dim FindString As String
    Dim Rng As Range
    FindString = Range("e11").Value
    
    If Trim(FindString) <> "" Then
        With Sheets("manager 1").Range("f:f")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            
            If Not Rng Is Nothing Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                .Calculation = xlCalculationManual
                .Goto Rng, False
            'End With
                    With ActiveCell
                    .Offset(0, 83).Value = ""
                      .Offset(0, 82).Value = ""
                        .Offset(0, 81).Value = ""
                          .Offset(0, 84).Value = ""
                          .Offset(0, 85).Value = ""
                            .Offset(0, 86).Value = ""
                              .Offset(0, 87).Value = ""
                                .Offset(0, 88).Value = ""
                                  .Offset(0, 89).Value = ""
                        End With
                'With Application
                  
                   .EnableEvents = True
                   .Calculation = xlCalculationAutomatic
                   
               
                
                
               ' Call ViewManager1
 Application.ScreenUpdating = True
            
        End With
    
     End If
     End With
     End If
End Sub

 

[AlphaFrog]

Try this (Not tested).

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Variant[/color], NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    r = Application.Match(Sheets("one-pager profile").Range("E11").Value, Sheets("MANAGER 1").Range("F:F"), [color=darkblue]False[/color])
    [color=darkblue]If[/color] IsNumeric(r) [color=darkblue]Then[/color]
        [color=darkblue]With[/color] Sheets("leavers")
            NextRow = .Range("CA" & Rows.Count).End(xlUp).Offset(1, 0).Row
            .Range("CH" & NextRow).Resize(1, 9).Value = Sheets("MANAGER 1").Range("CH" & r).Resize(1, 9).Value
            .Range("CA" & NextRow) = "leaver"
            .Range("CB" & NextRow) = UserForm5.TextBox29.Value    [color=green]' leaver date[/color]
            .Range("CC" & NextRow) = UserForm5.TextBox1.Value     [color=green]' leaver comments[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        Sheets("MANAGER 1").Range("CH" & r).Resize(1, 9).ClearContents
    [color=darkblue]Else[/color]
        MsgBox Sheets("one-pager profile").Range("E11").Value, vbExclamation, "No Match Found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[Celticfc]

Works amazing - processing time is now 7 seconds, thank you.