Help shorten loooong code?

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
203
Office Version
  1. 2003 or older
Platform
  1. Windows
Once again I find myself in need of help. Thank You for reading and helping with this. The code below works but is extremely tedious and long! Is there a way I can shorten it? At line 34 it gets repetitive for 30 Emp pages. Then again as line 305, once again for 30 instances. Help will be very much appreciated.
VBA Code: 
Private Sub CommandButton1_Click()
    vUserInput = MsgBox("This Action Will Update Input Information." & vbCrLf & " " & vbCrLf & "                            Continue?", vbYesNo, "Employee Updater")
    Select Case vUserInput
        Case vbYes
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect
    Next ws
        Sheets("Employee Info").Range("P5").Formula = "=CONCATENATE($C5, "" "",$E5, "" "",$G5)"
    Range("P5").AutoFill Destination:=Range("P5:P34"), Type:=xlFillDefault
    Sheets("Employee Info").Range("Q5").Formula = "=CONCATENATE(C5, "" "",E5)"
    Range("Q5").AutoFill Destination:=Range("Q5:Q34"), Type:=xlFillDefault
        Sheets("Employee Info").Range("P5:P34").Copy
    Sheets("Personal Graphs").Range("R6").PasteSpecial Paste:=xlPasteValues
    Sheets("Hours Graph").Range("S5").PasteSpecial Paste:=xlPasteValues
    Sheets("UnsatNotice").Range("BS1").PasteSpecial Paste:=xlPasteValues
        Sheets("Employee Info").Range("P5:Q34").Copy
    Sheets("Employee Info").Range("P5").PasteSpecial Paste:=xlPasteValues
    Sheets("Employee Info").Range("A1").Select
    Sheets("Employee Info").Range("Q5:Q34").Copy   'Overtime Employee First Name & Middle Initial'
    Sheets("Overtime").Range("Z1").PasteSpecial Paste:=xlPasteValues
    Sheets("Employee Info").Range("G5:G34").Copy   'Overtime Employee Last Name'
    Sheets("Overtime").Range("AA1").PasteSpecial Paste:=xlPasteValues
        Sheets("Employee Info").Range("I5:I34").Copy   'Overtime Employee Payrate'
    Sheets("Overtime").Range("AB1").PasteSpecial Paste:=xlPasteValues
    Sheets("Employee Info").Range("J5:J34").Copy   'Overtime Employee #'
    Sheets("Overtime").Range("Y1").PasteSpecial Paste:=xlPasteValues
    Sheets("Employee Info").Range("J5:J34").Copy
    Sheets("Quarterly Report").Range("BA9").PasteSpecial Paste:=xlPasteValues 'Quarterly Report Employee Number'
    Sheets("Employee Info").Range("P5:P34").Copy   'Quarterly Report Employee Name'
    Sheets("Quarterly Report").Range("AZ9").PasteSpecial Paste:=xlPasteValues
        'Here's where it gets long.''
        Sheets("Emp1").Range("D1").Value = Sheets("Employee Info").Range("P5").Value    'Employee Info to Name Emp 1'
    Sheets("Emp1").Range("D2").Value = Sheets("Employee Info").Range("J5").Value    'Employee Info to Emp # Emp 1'
    Sheets("Tally Sheet").Range("C21").Value = Sheets("Employee Info").Range("J5").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Tally Sheet").Range("B21").Value = Sheets("Employee Info").Range("P5").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B11").Value = Sheets("Employee Info").Range("P5").Value  'Name On Payroll'
    Sheets("Payroll").Range("B10").Value = Sheets("Employee Info").Range("J5").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C10").Value = Sheets("Employee Info").Range("K5").Value  'EMployee Assignment# On Payroll'
    Sheets("Warning").Range("B5").Value = Sheets("Employee Info").Range("P5").Value  'Name On Warning'
    Sheets("Warning").Range("J5").Value = Sheets("Employee Info").Range("H5").Value  'Birthdate On Warning'
        Sheets("Emp2").Range("D1").Value = Sheets("Employee Info").Range("P6").Value    'Tally Sheet Name Emp 2'
    Sheets("Emp2").Range("D2").Value = Sheets("Employee Info").Range("J6").Value    'Tally Sheet Emp # Emp 2'
    Sheets("Tally Sheet").Range("C31").Value = Sheets("Employee Info").Range("J6").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Tally Sheet").Range("B31").Value = Sheets("Employee Info").Range("P6").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B14").Value = Sheets("Employee Info").Range("J6").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C14").Value = Sheets("Employee Info").Range("K6").Value  'EMployee Assignment# On Payroll'
    Sheets("Payroll").Range("B15").Value = Sheets("Employee Info").Range("P6").Value  'Name On Payroll'
    Sheets("Warning").Range("B8").Value = Sheets("Employee Info").Range("P6").Value  'Name On Warning'
    Sheets("Warning").Range("J8").Value = Sheets("Employee Info").Range("H6").Value  'Birthdate On Warning'
        Sheets("Emp3").Range("D1").Value = Sheets("Employee Info").Range("P7").Value    'Tally Sheet Name Emp 3'
    Sheets("Emp3").Range("D2").Value = Sheets("Employee Info").Range("J7").Value    'Tally Sheet Emp # Emp 3'
    Sheets("Tally Sheet").Range("C41").Value = Sheets("Employee Info").Range("J7").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Tally Sheet").Range("B41").Value = Sheets("Employee Info").Range("P7").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B18").Value = Sheets("Employee Info").Range("J7").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C18").Value = Sheets("Employee Info").Range("K7").Value  'EMployee Assignment# On Payroll'
    Sheets("Payroll").Range("B19").Value = Sheets("Employee Info").Range("P7").Value  'Name On Payroll'
    Sheets("Warning").Range("B11").Value = Sheets("Employee Info").Range("P7").Value  'Name On Warning'
    Sheets("Warning").Range("J11").Value = Sheets("Employee Info").Range("H7").Value  'Birthdate On Warning'
        Sheets("Emp4").Range("D1").Value = Sheets("Employee Info").Range("P8").Value    'Tally Sheet Name Emp 4'
    Sheets("Emp4").Range("D2").Value = Sheets("Employee Info").Range("J8").Value    'Tally Sheet Emp # Emp 4'
    Sheets("Tally Sheet").Range("C51").Value = Sheets("Employee Info").Range("J8").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B22").Value = Sheets("Employee Info").Range("J8").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C22").Value = Sheets("Employee Info").Range("K8").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B51").Value = Sheets("Employee Info").Range("P8").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B23").Value = Sheets("Employee Info").Range("P8").Value  'Name On Payroll'
    Sheets("Warning").Range("B14").Value = Sheets("Employee Info").Range("P8").Value  'Name On Warning'
    Sheets("Warning").Range("J14").Value = Sheets("Employee Info").Range("H8").Value  'Birthdate On Warning'
        Sheets("Emp5").Range("D1").Value = Sheets("Employee Info").Range("P9").Value    'Tally Sheet Name Emp 5'
    Sheets("Emp5").Range("D2").Value = Sheets("Employee Info").Range("J9").Value    'Tally Sheet Emp # Emp 5'
    Sheets("Tally Sheet").Range("C61").Value = Sheets("Employee Info").Range("J9").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B26").Value = Sheets("Employee Info").Range("J9").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C26").Value = Sheets("Employee Info").Range("K9").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B61").Value = Sheets("Employee Info").Range("P9").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B27").Value = Sheets("Employee Info").Range("P9").Value  'Name On Payroll'
    Sheets("Warning").Range("B17").Value = Sheets("Employee Info").Range("P9").Value  'Name On Warning'
    Sheets("Warning").Range("J17").Value = Sheets("Employee Info").Range("H9").Value  'Birthdate On Warning'
        Sheets("Emp6").Range("D1").Value = Sheets("Employee Info").Range("P10").Value    'Tally Sheet Name Emp 6'
    Sheets("Emp6").Range("D2").Value = Sheets("Employee Info").Range("J10").Value    'Tally Sheet Emp # Emp 6'
    Sheets("Tally Sheet").Range("C71").Value = Sheets("Employee Info").Range("J10").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B30").Value = Sheets("Employee Info").Range("J10").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C30").Value = Sheets("Employee Info").Range("K10").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B71").Value = Sheets("Employee Info").Range("P10").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B31").Value = Sheets("Employee Info").Range("P10").Value  'Name On Payroll'
    Sheets("Warning").Range("B20").Value = Sheets("Employee Info").Range("P10").Value  'Name On Warning'
    Sheets("Warning").Range("J20").Value = Sheets("Employee Info").Range("H10").Value  'Birthdate On Warning'
        Sheets("Emp7").Range("D1").Value = Sheets("Employee Info").Range("P11").Value    'Tally Sheet Name Emp 7'
    Sheets("Emp7").Range("D2").Value = Sheets("Employee Info").Range("J11").Value    'Tally Sheet Emp # Emp 7'
    Sheets("Tally Sheet").Range("C81").Value = Sheets("Employee Info").Range("J11").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B34").Value = Sheets("Employee Info").Range("J11").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C34").Value = Sheets("Employee Info").Range("K11").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B81").Value = Sheets("Employee Info").Range("P11").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B35").Value = Sheets("Employee Info").Range("P11").Value  'Name On Payroll'
    Sheets("Warning").Range("B23").Value = Sheets("Employee Info").Range("P11").Value  'Name On Warning'
    Sheets("Warning").Range("J23").Value = Sheets("Employee Info").Range("H11").Value  'Birthdate On Warning'
        Sheets("Emp8").Range("D1").Value = Sheets("Employee Info").Range("P12").Value    'Tally Sheet Name Emp 8'
    Sheets("Emp8").Range("D2").Value = Sheets("Employee Info").Range("J12").Value    'Tally Sheet Emp # Emp 8'
    Sheets("Tally Sheet").Range("C91").Value = Sheets("Employee Info").Range("J12").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B38").Value = Sheets("Employee Info").Range("J12").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C38").Value = Sheets("Employee Info").Range("K12").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B91").Value = Sheets("Employee Info").Range("P12").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B39").Value = Sheets("Employee Info").Range("P12").Value  'Name On Payroll'
    Sheets("Warning").Range("B26").Value = Sheets("Employee Info").Range("P12").Value  'Name On Warning'
    Sheets("Warning").Range("J26").Value = Sheets("Employee Info").Range("H12").Value  'Birthdate On Warning'
        Sheets("Emp9").Range("D1").Value = Sheets("Employee Info").Range("P13").Value    'Tally Sheet Name Emp 9'
    Sheets("Emp9").Range("D2").Value = Sheets("Employee Info").Range("J13").Value    'Tally Sheet Emp # Emp 9'
    Sheets("Tally Sheet").Range("C101").Value = Sheets("Employee Info").Range("J13").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B42").Value = Sheets("Employee Info").Range("J13").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C42").Value = Sheets("Employee Info").Range("K13").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B101").Value = Sheets("Employee Info").Range("P13").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B43").Value = Sheets("Employee Info").Range("P13").Value  'Name On Payroll'
    Sheets("Warning").Range("B29").Value = Sheets("Employee Info").Range("P13").Value  'Name On Warning'
    Sheets("Warning").Range("J29").Value = Sheets("Employee Info").Range("H13").Value  'Birthdate On Warning'
        Sheets("Emp10").Range("D1").Value = Sheets("Employee Info").Range("P14").Value    'Tally Sheet Name Emp 10'
    Sheets("Emp10").Range("D2").Value = Sheets("Employee Info").Range("J14").Value    'Tally Sheet Emp # Emp 10'
    Sheets("Tally Sheet").Range("C111").Value = Sheets("Employee Info").Range("J14").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B46").Value = Sheets("Employee Info").Range("J14").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C46").Value = Sheets("Employee Info").Range("K14").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B111").Value = Sheets("Employee Info").Range("P14").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B47").Value = Sheets("Employee Info").Range("P14").Value  'Name On Payroll'
    Sheets("Warning").Range("B32").Value = Sheets("Employee Info").Range("P14").Value  'Name On Warning'
    Sheets("Warning").Range("J32").Value = Sheets("Employee Info").Range("H14").Value  'Birthdate On Warning'
        Sheets("Emp11").Range("D1").Value = Sheets("Employee Info").Range("P15").Value    'Tally Sheet Name Emp 11'
    Sheets("Emp11").Range("D2").Value = Sheets("Employee Info").Range("J15").Value    'Tally Sheet Emp # Emp 11'
    Sheets("Tally Sheet").Range("C121").Value = Sheets("Employee Info").Range("J15").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B50").Value = Sheets("Employee Info").Range("J15").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C50").Value = Sheets("Employee Info").Range("K15").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B121").Value = Sheets("Employee Info").Range("P15").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B51").Value = Sheets("Employee Info").Range("P15").Value  'Name On Payroll'
    Sheets("Warning").Range("B35").Value = Sheets("Employee Info").Range("P15").Value  'Name On Warning'
    Sheets("Warning").Range("J35").Value = Sheets("Employee Info").Range("H15").Value  'Birthdate On Warning'
        Sheets("Emp12").Range("D1").Value = Sheets("Employee Info").Range("P16").Value    'Tally Sheet Name Emp 12'
    Sheets("Emp12").Range("D2").Value = Sheets("Employee Info").Range("J16").Value    'Tally Sheet Emp # Emp 12'
    Sheets("Tally Sheet").Range("C131").Value = Sheets("Employee Info").Range("J16").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B54").Value = Sheets("Employee Info").Range("J16").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C54").Value = Sheets("Employee Info").Range("K16").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B131").Value = Sheets("Employee Info").Range("P16").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B55").Value = Sheets("Employee Info").Range("P16").Value  'Name On Payroll'
    Sheets("Warning").Range("B38").Value = Sheets("Employee Info").Range("P16").Value  'Name On Warning'
    Sheets("Warning").Range("J38").Value = Sheets("Employee Info").Range("H16").Value  'Birthdate On Warning'
        Sheets("Emp13").Range("D1").Value = Sheets("Employee Info").Range("P17").Value    'Tally Sheet Name Emp 13'
    Sheets("Emp13").Range("D2").Value = Sheets("Employee Info").Range("J17").Value    'Tally Sheet Emp # Emp 13'
    Sheets("Tally Sheet").Range("C141").Value = Sheets("Employee Info").Range("J17").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B58").Value = Sheets("Employee Info").Range("J17").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C58").Value = Sheets("Employee Info").Range("K17").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B141").Value = Sheets("Employee Info").Range("P17").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B59").Value = Sheets("Employee Info").Range("P17").Value  'Name On Payroll'
    Sheets("Warning").Range("B41").Value = Sheets("Employee Info").Range("P17").Value  'Name On Warning'
    Sheets("Warning").Range("J41").Value = Sheets("Employee Info").Range("H17").Value  'Birthdate On Warning'
        Sheets("Emp14").Range("D1").Value = Sheets("Employee Info").Range("P18").Value    'Tally Sheet Name Emp 14'
    Sheets("Emp14").Range("D2").Value = Sheets("Employee Info").Range("J18").Value    'Tally Sheet Emp # Emp 14'
    Sheets("Tally Sheet").Range("C151").Value = Sheets("Employee Info").Range("J18").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B62").Value = Sheets("Employee Info").Range("J18").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C62").Value = Sheets("Employee Info").Range("K18").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B151").Value = Sheets("Employee Info").Range("P18").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B63").Value = Sheets("Employee Info").Range("P18").Value  'Name On Payroll'
    Sheets("Warning").Range("B44").Value = Sheets("Employee Info").Range("P18").Value  'Name On Warning'
    Sheets("Warning").Range("J44").Value = Sheets("Employee Info").Range("H18").Value  'Birthdate On Warning'
        Sheets("Emp15").Range("D1").Value = Sheets("Employee Info").Range("P19").Value    'Tally Sheet Name Emp 15'
    Sheets("Emp15").Range("D2").Value = Sheets("Employee Info").Range("J19").Value    'Tally Sheet Emp # Emp 15'
    Sheets("Tally Sheet").Range("C161").Value = Sheets("Employee Info").Range("J19").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B66").Value = Sheets("Employee Info").Range("J19").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C66").Value = Sheets("Employee Info").Range("K19").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B161").Value = Sheets("Employee Info").Range("P19").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B67").Value = Sheets("Employee Info").Range("P19").Value  'Name On Payroll'
    Sheets("Warning").Range("B47").Value = Sheets("Employee Info").Range("P19").Value  'Name On Warning'
    Sheets("Warning").Range("J47").Value = Sheets("Employee Info").Range("H19").Value  'Birthdate On Warning'
        Sheets("Emp16").Range("D1").Value = Sheets("Employee Info").Range("P20").Value    'Tally Sheet Name Emp 16'
    Sheets("Emp16").Range("D2").Value = Sheets("Employee Info").Range("J20").Value    'Tally Sheet Emp # Emp 16'
    Sheets("Tally Sheet").Range("C171").Value = Sheets("Employee Info").Range("J20").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B70").Value = Sheets("Employee Info").Range("J20").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C70").Value = Sheets("Employee Info").Range("K20").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B171").Value = Sheets("Employee Info").Range("P20").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B71").Value = Sheets("Employee Info").Range("P20").Value  'Name On Payroll'
    Sheets("Warning").Range("B50").Value = Sheets("Employee Info").Range("P20").Value  'Name On Warning'
    Sheets("Warning").Range("J50").Value = Sheets("Employee Info").Range("H20").Value  'Birthdate On Warning'
        Sheets("Emp17").Range("D1").Value = Sheets("Employee Info").Range("P21").Value    'Tally Sheet Name Emp 17'
    Sheets("Emp17").Range("D2").Value = Sheets("Employee Info").Range("J21").Value    'Tally Sheet Emp # Emp 17'
    Sheets("Tally Sheet").Range("C181").Value = Sheets("Employee Info").Range("J21").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B74").Value = Sheets("Employee Info").Range("J21").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C74").Value = Sheets("Employee Info").Range("K21").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B181").Value = Sheets("Employee Info").Range("P21").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B75").Value = Sheets("Employee Info").Range("P21").Value  'Name On Payroll'
    Sheets("Warning").Range("B53").Value = Sheets("Employee Info").Range("P21").Value  'Name On Warning'
    Sheets("Warning").Range("J53").Value = Sheets("Employee Info").Range("H21").Value  'Birthdate On Warning'
        Sheets("Emp18").Range("D1").Value = Sheets("Employee Info").Range("P22").Value    'Tally Sheet Name Emp 18'
    Sheets("Emp18").Range("D2").Value = Sheets("Employee Info").Range("J22").Value    'Tally Sheet Emp # Emp 18'
    Sheets("Tally Sheet").Range("C191").Value = Sheets("Employee Info").Range("J22").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B78").Value = Sheets("Employee Info").Range("J22").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C78").Value = Sheets("Employee Info").Range("K22").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B191").Value = Sheets("Employee Info").Range("P22").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B79").Value = Sheets("Employee Info").Range("P22").Value  'Name On Payroll'
    Sheets("Warning").Range("B56").Value = Sheets("Employee Info").Range("P22").Value  'Name On Warning'
    Sheets("Warning").Range("J56").Value = Sheets("Employee Info").Range("H22").Value  'Birthdate On Warning'
        Sheets("Emp19").Range("D1").Value = Sheets("Employee Info").Range("P23").Value    'Tally Sheet Name Emp 19'
    Sheets("Emp19").Range("D2").Value = Sheets("Employee Info").Range("J23").Value    'Tally Sheet Emp # Emp 19'
    Sheets("Tally Sheet").Range("C201").Value = Sheets("Employee Info").Range("J23").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B82").Value = Sheets("Employee Info").Range("J23").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C82").Value = Sheets("Employee Info").Range("K23").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B201").Value = Sheets("Employee Info").Range("P23").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B83").Value = Sheets("Employee Info").Range("P23").Value  'Name On Payroll'
    Sheets("Warning").Range("B59").Value = Sheets("Employee Info").Range("P23").Value  'Name On Warning'
    Sheets("Warning").Range("J59").Value = Sheets("Employee Info").Range("H23").Value  'Birthdate On Warning'
        Sheets("Emp20").Range("D1").Value = Sheets("Employee Info").Range("P24").Value    'Tally Sheet Name Emp 20'
    Sheets("Emp20").Range("D2").Value = Sheets("Employee Info").Range("J24").Value    'Tally Sheet Emp # Emp 20'
    Sheets("Tally Sheet").Range("C211").Value = Sheets("Employee Info").Range("J24").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B86").Value = Sheets("Employee Info").Range("J24").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C86").Value = Sheets("Employee Info").Range("K24").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B211").Value = Sheets("Employee Info").Range("P24").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B87").Value = Sheets("Employee Info").Range("P24").Value  'Name On Payroll'
    Sheets("Warning").Range("B62").Value = Sheets("Employee Info").Range("P24").Value  'Name On Warning'
    Sheets("Warning").Range("J62").Value = Sheets("Employee Info").Range("H24").Value  'Birthdate On Warning'
        Sheets("Emp21").Range("D1").Value = Sheets("Employee Info").Range("P25").Value    'Tally Sheet Name Emp 21'
    Sheets("Emp21").Range("D2").Value = Sheets("Employee Info").Range("J25").Value    'Tally Sheet Emp # Emp 21'
    Sheets("Tally Sheet").Range("C221").Value = Sheets("Employee Info").Range("J25").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B90").Value = Sheets("Employee Info").Range("J25").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C90").Value = Sheets("Employee Info").Range("K25").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B221").Value = Sheets("Employee Info").Range("P25").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B91").Value = Sheets("Employee Info").Range("P25").Value  'Name On Payroll'
    Sheets("Warning").Range("B65").Value = Sheets("Employee Info").Range("P25").Value  'Name On Warning'
    Sheets("Warning").Range("J65").Value = Sheets("Employee Info").Range("H25").Value  'Birthdate On Warning'
        Sheets("Emp22").Range("D1").Value = Sheets("Employee Info").Range("P26").Value    'Tally Sheet Name Emp 22'
    Sheets("Emp22").Range("D2").Value = Sheets("Employee Info").Range("J26").Value    'Tally Sheet Emp # Emp 22'
    Sheets("Tally Sheet").Range("C231").Value = Sheets("Employee Info").Range("J26").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B94").Value = Sheets("Employee Info").Range("J26").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C94").Value = Sheets("Employee Info").Range("K26").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B231").Value = Sheets("Employee Info").Range("P26").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B95").Value = Sheets("Employee Info").Range("P26").Value  'Name On Payroll'
    Sheets("Warning").Range("B68").Value = Sheets("Employee Info").Range("P26").Value  'Name On Warning'
    Sheets("Warning").Range("J68").Value = Sheets("Employee Info").Range("H26").Value  'Birthdate On Warning'
        Sheets("Emp23").Range("D1").Value = Sheets("Employee Info").Range("P27").Value    'Tally Sheet Name Emp 23'
    Sheets("Emp23").Range("D2").Value = Sheets("Employee Info").Range("J27").Value    'Tally Sheet Emp # Emp 23'
    Sheets("Tally Sheet").Range("C241").Value = Sheets("Employee Info").Range("J27").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B98").Value = Sheets("Employee Info").Range("J27").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C98").Value = Sheets("Employee Info").Range("K27").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B241").Value = Sheets("Employee Info").Range("P27").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B99").Value = Sheets("Employee Info").Range("P27").Value  'Name On Payroll'
    Sheets("Warning").Range("B71").Value = Sheets("Employee Info").Range("P27").Value  'Name On Warning'
    Sheets("Warning").Range("J71").Value = Sheets("Employee Info").Range("H27").Value  'Birthdate On Warning'
        Sheets("Emp24").Range("D1").Value = Sheets("Employee Info").Range("P28").Value    'Tally Sheet Name Emp 24'
    Sheets("Emp24").Range("D2").Value = Sheets("Employee Info").Range("J28").Value    'Tally Sheet Emp # Emp 24'
    Sheets("Tally Sheet").Range("C251").Value = Sheets("Employee Info").Range("J28").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B102").Value = Sheets("Employee Info").Range("J28").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C102").Value = Sheets("Employee Info").Range("K28").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B251").Value = Sheets("Employee Info").Range("P28").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B103").Value = Sheets("Employee Info").Range("P28").Value  'Name On Payroll'
    Sheets("Warning").Range("B74").Value = Sheets("Employee Info").Range("P28").Value  'Name On Warning'
    Sheets("Warning").Range("J74").Value = Sheets("Employee Info").Range("H28").Value  'Birthdate On Warning'        
        Sheets("Emp25").Range("D1").Value = Sheets("Employee Info").Range("P29").Value    'Tally Sheet Name Emp 25'
    Sheets("Emp25").Range("D2").Value = Sheets("Employee Info").Range("J29").Value    'Tally Sheet Emp # Emp 25'
    Sheets("Tally Sheet").Range("C261").Value = Sheets("Employee Info").Range("J29").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B106").Value = Sheets("Employee Info").Range("J29").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C106").Value = Sheets("Employee Info").Range("K29").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B261").Value = Sheets("Employee Info").Range("P29").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B107").Value = Sheets("Employee Info").Range("P29").Value  'Name On Payroll'
    Sheets("Warning").Range("B77").Value = Sheets("Employee Info").Range("P29").Value  'Name On Warning'
    Sheets("Warning").Range("J77").Value = Sheets("Employee Info").Range("H29").Value  'Birthdate On Warning'        
        Sheets("Emp26").Range("D1").Value = Sheets("Employee Info").Range("P30").Value    'Tally Sheet Name Emp 26'
    Sheets("Emp26").Range("D2").Value = Sheets("Employee Info").Range("J30").Value    'Tally Sheet Emp # Emp 26'
    Sheets("Tally Sheet").Range("C271").Value = Sheets("Employee Info").Range("J30").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B110").Value = Sheets("Employee Info").Range("J30").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C110").Value = Sheets("Employee Info").Range("K30").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B271").Value = Sheets("Employee Info").Range("P30").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B111").Value = Sheets("Employee Info").Range("P30").Value  'Name On Payroll'
    Sheets("Warning").Range("B80").Value = Sheets("Employee Info").Range("P30").Value  'Name On Warning'
    Sheets("Warning").Range("J80").Value = Sheets("Employee Info").Range("H30").Value  'Birthdate On Warning'    
        Sheets("Emp27").Range("D1").Value = Sheets("Employee Info").Range("P31").Value    'Tally Sheet Name Emp 27'
    Sheets("Emp27").Range("D2").Value = Sheets("Employee Info").Range("J31").Value    'Tally Sheet Emp # Emp 27'
    Sheets("Tally Sheet").Range("C281").Value = Sheets("Employee Info").Range("J31").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B114").Value = Sheets("Employee Info").Range("J31").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C114").Value = Sheets("Employee Info").Range("K31").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B281").Value = Sheets("Employee Info").Range("P31").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B115").Value = Sheets("Employee Info").Range("P31").Value  'Name On Payroll'
    Sheets("Warning").Range("B83").Value = Sheets("Employee Info").Range("P31").Value  'Name On Warning'
    Sheets("Warning").Range("J83").Value = Sheets("Employee Info").Range("H31").Value  'Birthdate On Warning'        
        Sheets("Emp28").Range("D1").Value = Sheets("Employee Info").Range("P32").Value    'Tally Sheet Name Emp 28'
    Sheets("Emp28").Range("D2").Value = Sheets("Employee Info").Range("J32").Value    'Tally Sheet Emp # Emp 28'
    Sheets("Payroll").Range("B118").Value = Sheets("Employee Info").Range("J32").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C118").Value = Sheets("Employee Info").Range("K32").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("C291").Value = Sheets("Employee Info").Range("J32").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Tally Sheet").Range("B291").Value = Sheets("Employee Info").Range("P32").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B119").Value = Sheets("Employee Info").Range("P32").Value  'Name On Payroll'
    Sheets("Warning").Range("B86").Value = Sheets("Employee Info").Range("P32").Value  'Name On Warning'
    Sheets("Warning").Range("J86").Value = Sheets("Employee Info").Range("H32").Value  'Birthdate On Warning'
        Sheets("Emp29").Range("D1").Value = Sheets("Employee Info").Range("P33").Value    'Tally Sheet Name Emp 29'
    Sheets("Emp29").Range("D2").Value = Sheets("Employee Info").Range("J33").Value    'Tally Sheet Emp # Emp 29'
    Sheets("Tally Sheet").Range("C301").Value = Sheets("Employee Info").Range("J33").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B122").Value = Sheets("Employee Info").Range("J33").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C122").Value = Sheets("Employee Info").Range("K33").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B301").Value = Sheets("Employee Info").Range("P33").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B123").Value = Sheets("Employee Info").Range("P33").Value  'Name On Payroll'
    Sheets("Warning").Range("B89").Value = Sheets("Employee Info").Range("P33").Value  'Name On Warning'
    Sheets("Warning").Range("J89").Value = Sheets("Employee Info").Range("H33").Value  'Birthdate On Warning'
        Sheets("Emp30").Range("D1").Value = Sheets("Employee Info").Range("P34").Value    'Tally Sheet Name Emp 30'
    Sheets("Emp30").Range("D2").Value = Sheets("Employee Info").Range("J34").Value    'Tally Sheet Emp # Emp 30'
    Sheets("Tally Sheet").Range("C311").Value = Sheets("Employee Info").Range("J34").Value    'Employee Info Employee# to Tally Sheet'
    Sheets("Payroll").Range("B126").Value = Sheets("Employee Info").Range("J34").Value  'EMployee# On Payroll'
    Sheets("Payroll").Range("C126").Value = Sheets("Employee Info").Range("K34").Value  'EMployee Assignment# On Payroll'
    Sheets("Tally Sheet").Range("B311").Value = Sheets("Employee Info").Range("P34").Value  'Name Of Tally Sheet'
    Sheets("Payroll").Range("B127").Value = Sheets("Employee Info").Range("P34").Value  'Name On Payroll'
    Sheets("Warning").Range("B92").Value = Sheets("Employee Info").Range("P34").Value  'Name On Warning'
    Sheets("Warning").Range("J92").Value = Sheets("Employee Info").Range("H34").Value  'Birthdate On Warning'
    
    If Range("H5").Value > 0 Then
    Sheets("Warning").Range("J5") = Sheets("Employee Info").Range("H5").Value       'Birth Date Emp 1'
    ElseIf IsEmpty(Range("H5")) = True Then
    Sheets("Warning").Range("J5") = "Need Info"
    End If
    
    If Range("H6").Value > 0 Then
    Sheets("Warning").Range("J8") = Sheets("Employee Info").Range("H6").Value       'Birth Date Emp 2'
    ElseIf IsEmpty(Range("H6")) = True Then
    Sheets("Warning").Range("J8") = "Need Info"
    End If
    
    If Range("H7").Value > 0 Then
    Sheets("Warning").Range("J11") = Sheets("Employee Info").Range("H7").Value       'Birth Date Emp 3'
    ElseIf IsEmpty(Range("H7")) = True Then
    Sheets("Warning").Range("J11") = "Need Info"
    End If
    
    If Range("H8").Value > 0 Then
    Sheets("Warning").Range("J14") = Sheets("Employee Info").Range("H8").Value       'Birth Date Emp 4'
    ElseIf IsEmpty(Range("H8")) = True Then
    Sheets("Warning").Range("J14") = "Need Info"
    End If
    
    If Range("H9").Value > 0 Then
    Sheets("Warning").Range("J17") = Sheets("Employee Info").Range("H9").Value       'Birth Date Emp 5'
    ElseIf IsEmpty(Range("H9")) = True Then
    Sheets("Warning").Range("J17") = "Need Info"
    End If
    
    If Range("H10").Value > 0 Then
    Sheets("Warning").Range("J20") = Sheets("Employee Info").Range("H10").Value       'Birth Date Emp 6'
    ElseIf IsEmpty(Range("H10")) = True Then
    Sheets("Warning").Range("J20") = "Need Info"
    End If
    
    If Range("H11").Value > 0 Then
    Sheets("Warning").Range("J23") = Sheets("Employee Info").Range("H11").Value       'Birth Date Emp 7'
    ElseIf IsEmpty(Range("H11")) = True Then
    Sheets("Warning").Range("J23") = "Need Info"
    End If
    
    If Range("H12").Value > 0 Then
    Sheets("Warning").Range("J26") = Sheets("Employee Info").Range("H12").Value       'Birth Date Emp 8'
    ElseIf IsEmpty(Range("H12")) = True Then
    Sheets("Warning").Range("J26") = "Need Info"
    End If
    
    If Range("H13").Value > 0 Then
    Sheets("Warning").Range("J29") = Sheets("Employee Info").Range("H13").Value       'Birth Date Emp 9'
    ElseIf IsEmpty(Range("H13")) = True Then
    Sheets("Warning").Range("J29") = "Need Info"
    End If
    
    If Range("H14").Value > 0 Then
    Sheets("Warning").Range("J32") = Sheets("Employee Info").Range("H14").Value       'Birth Date Emp 10'
    ElseIf IsEmpty(Range("H14")) = True Then
    Sheets("Warning").Range("J32") = "Need Info"
    End If
    
    If Range("H15").Value > 0 Then
    Sheets("Warning").Range("J35") = Sheets("Employee Info").Range("H15").Value       'Birth Date Emp 11'
    ElseIf IsEmpty(Range("H15")) = True Then
    Sheets("Warning").Range("J35") = "Need Info"
    End If
    
    If Range("H16").Value > 0 Then
    Sheets("Warning").Range("J38") = Sheets("Employee Info").Range("H16").Value       'Birth Date Emp 12'
    ElseIf IsEmpty(Range("H16")) = True Then
    Sheets("Warning").Range("J38") = "Need Info"
    End If
    
    If Range("H17").Value > 0 Then
    Sheets("Warning").Range("J41") = Sheets("Employee Info").Range("H17").Value       'Birth Date Emp 13'
    ElseIf IsEmpty(Range("H17")) = True Then
    Sheets("Warning").Range("J41") = "Need Info"
    End If
    
    If Range("H18").Value > 0 Then
    Sheets("Warning").Range("J44") = Sheets("Employee Info").Range("H18").Value       'Birth Date Emp 14'
    ElseIf IsEmpty(Range("H18")) = True Then
    Sheets("Warning").Range("J44") = "Need Info"
    End If
      
    If Range("H19").Value > 0 Then
    Sheets("Warning").Range("J47") = Sheets("Employee Info").Range("H19").Value       'Birth Date Emp 15'
    ElseIf IsEmpty(Range("H19")) = True Then
    Sheets("Warning").Range("J47") = "Need Info"
    End If
    
    If Range("H20").Value > 0 Then
    Sheets("Warning").Range("J50") = Sheets("Employee Info").Range("H20").Value       'Birth Date Emp 16'
    ElseIf IsEmpty(Range("H20")) = True Then
    Sheets("Warning").Range("J50") = "Need Info"
    End If
    
    If Range("H21").Value > 0 Then
    Sheets("Warning").Range("J53") = Sheets("Employee Info").Range("H21").Value       'Birth Date Emp 17'
    ElseIf IsEmpty(Range("H21")) = True Then
    Sheets("Warning").Range("J53") = "Need Info"
    End If
    
    If Range("H22").Value > 0 Then
    Sheets("Warning").Range("J56") = Sheets("Employee Info").Range("H22").Value       'Birth Date Emp 18'
    ElseIf IsEmpty(Range("H22")) = True Then
    Sheets("Warning").Range("J56") = "Need Info"
    End If
    
    If Range("H23").Value > 0 Then
    Sheets("Warning").Range("J59") = Sheets("Employee Info").Range("H23").Value       'Birth Date Emp 19'
    ElseIf IsEmpty(Range("H23")) = True Then
    Sheets("Warning").Range("J59") = "Need Info"
    End If
    
    If Range("H24").Value > 0 Then
    Sheets("Warning").Range("J62") = Sheets("Employee Info").Range("H24").Value       'Birth Date Emp 20'
    ElseIf IsEmpty(Range("H24")) = True Then
    Sheets("Warning").Range("J62") = "Need Info"
    End If
    
    If Range("H25").Value > 0 Then
    Sheets("Warning").Range("J65") = Sheets("Employee Info").Range("H25").Value       'Birth Date Emp 21'
    ElseIf IsEmpty(Range("H25")) = True Then
    Sheets("Warning").Range("J65") = "Need Info"
    End If
    
    If Range("H26").Value > 0 Then
    Sheets("Warning").Range("J68") = Sheets("Employee Info").Range("H26").Value       'Birth Date Emp 22'
    ElseIf IsEmpty(Range("H25")) = True Then
    Sheets("Warning").Range("J68") = "Need Info"
    End If
    
    If Range("H27").Value > 0 Then
    Sheets("Warning").Range("J71") = Sheets("Employee Info").Range("H27").Value       'Birth Date Emp 23'
    ElseIf IsEmpty(Range("H27")) = True Then
    Sheets("Warning").Range("J71") = "Need Info"
    End If
    
    If Range("H28").Value > 0 Then
    Sheets("Warning").Range("J74") = Sheets("Employee Info").Range("H28").Value       'Birth Date Emp 24'
    ElseIf IsEmpty(Range("H28")) = True Then
    Sheets("Warning").Range("J74") = "Need Info"
    End If
    
    If Range("H29").Value > 0 Then
    Sheets("Warning").Range("J77") = Sheets("Employee Info").Range("H29").Value       'Birth Date Emp 25'
    ElseIf IsEmpty(Range("H29")) = True Then
    Sheets("Warning").Range("J77") = "Need Info"
    End If
    
    If Range("H30").Value > 0 Then
    Sheets("Warning").Range("J80") = Sheets("Employee Info").Range("H30").Value       'Birth Date Emp 26'
    ElseIf IsEmpty(Range("H30")) = True Then
    Sheets("Warning").Range("J80") = "Need Info"
    End If
    
    If Range("H31").Value > 0 Then
    Sheets("Warning").Range("J83") = Sheets("Employee Info").Range("H31").Value       'Birth Date Emp 27'
    ElseIf IsEmpty(Range("H31")) = True Then
    Sheets("Warning").Range("J83") = "Need Info"
    End If
    
    If Range("H32").Value > 0 Then
    Sheets("Warning").Range("J86") = Sheets("Employee Info").Range("H32").Value       'Birth Date Emp 28'
    ElseIf IsEmpty(Range("H32")) = True Then
    Sheets("Warning").Range("J86") = "Need Info"
    End If
    
    If Range("H33").Value > 0 Then
    Sheets("Warning").Range("J89") = Sheets("Employee Info").Range("H33").Value       'Birth Date Emp 29'
    ElseIf IsEmpty(Range("H33")) = True Then
    Sheets("Warning").Range("J89") = "Need Info"
    End If
    
    If Range("H34").Value > 0 Then
    Sheets("Warning").Range("J92") = Sheets("Employee Info").Range("H34").Value       'Birth Date Emp 30'
    ElseIf IsEmpty(Range("H34")) = True Then
    Sheets("Warning").Range("J92") = "Need Info"
    End If
        For Each ws In ActiveWorkbook.Sheets
        ws.Activate
        ws.[a1].Select
        ActiveWorkbook.Worksheets(1).Activate
    ws.Protect
    Next ws
    Application.ScreenUpdating = True
    Call Tally_Name
    Case vbNo
    End Select
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try this:

VBA Code: 
Private Sub CommandButton1_Click()
  Dim vUserInput As VbMsgBoxResult
  Dim ws As Worksheet, shE As Worksheet
  Dim i As Long, j As Long, t As Long, p As Long, w As Long
  
  Application.ScreenUpdating = False
  
  vUserInput = MsgBox("This Action Will Update Input Information." & vbCrLf & " " & vbCrLf & _
                      "                            Continue?", vbYesNo, "Employee Updater")
  If vUserInput = vbNo Then Exit Sub
  
  For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect
  Next ws
  
  Set shE = Sheets("Employee Info")
  
  '*********
  shE.Range("P5").Formula = "=CONCATENATE($C5, "" "",$E5, "" "",$G5)"
  shE.Range("P5").AutoFill Destination:=shE.Range("P5:P34"), Type:=xlFillDefault
  shE.Range("Q5").Formula = "=CONCATENATE(C5, "" "",E5)"
  shE.Range("Q5").AutoFill Destination:=shE.Range("Q5:Q34"), Type:=xlFillDefault
  shE.Range("P5:P34").Copy
  Sheets("Personal Graphs").Range("R6").PasteSpecial Paste:=xlPasteValues
  Sheets("Hours Graph").Range("S5").PasteSpecial Paste:=xlPasteValues
  Sheets("UnsatNotice").Range("BS1").PasteSpecial Paste:=xlPasteValues
  shE.Range("P5:Q34").Copy
  shE.Range("P5").PasteSpecial Paste:=xlPasteValues
  shE.Range("A1").Select
  shE.Range("Q5:Q34").Copy   'Overtime Employee First Name & Middle Initial'
  Sheets("Overtime").Range("Z1").PasteSpecial Paste:=xlPasteValues
  shE.Range("G5:G34").Copy   'Overtime Employee Last Name'
  Sheets("Overtime").Range("AA1").PasteSpecial Paste:=xlPasteValues
  shE.Range("I5:I34").Copy   'Overtime Employee Payrate'
  Sheets("Overtime").Range("AB1").PasteSpecial Paste:=xlPasteValues
  shE.Range("J5:J34").Copy   'Overtime Employee #'
  Sheets("Overtime").Range("Y1").PasteSpecial Paste:=xlPasteValues
  shE.Range("J5:J34").Copy
  Sheets("Quarterly Report").Range("BA9").PasteSpecial Paste:=xlPasteValues 'Quarterly Report Employee Number'
  shE.Range("P5:P34").Copy   'Quarterly Report Employee Name'
  Sheets("Quarterly Report").Range("AZ9").PasteSpecial Paste:=xlPasteValues
  '*********

  j = 5
  t = 21
  p = 10
  w = 5
  For i = 1 To 30
    Sheets("Emp" & i).Range("D1").Value = shE.Range("P" & j).Value      'Employee Info to Name Emp 1'
    Sheets("Emp" & i).Range("D2").Value = shE.Range("J" & j).Value      'Employee Info to Emp # Emp 1'
    Sheets("Tally Sheet").Range("C" & t).Value = shE.Range("J" & j).Value 'Employee Info Employee# to Tally Sheet'
    Sheets("Tally Sheet").Range("B" & t).Value = shE.Range("P" & j).Value 'Name Of Tally Sheet'
    Sheets("Payroll").Range("B" & p).Value = shE.Range("J" & j).Value     'EMployee# On Payroll'
    Sheets("Payroll").Range("C" & p).Value = shE.Range("K" & j).Value     'EMployee Assignment# On Payroll'
    Sheets("Payroll").Range("B" & p + 1).Value = shE.Range("P" & j).Value    'Name On Payroll'
    Sheets("Warning").Range("B" & w).Value = shE.Range("P" & j).Value      'Name On Warning'
    Sheets("Warning").Range("J" & w).Value = shE.Range("H" & j).Value      'Birthdate On Warning'
    j = j + 1
    t = t + 10
    p = p + 4
    w = w + 3
  Next
  
  j = 5
  For i = 5 To 34
    If shE.Range("H" & i).Value > 0 Then Sheets("Warning").Range("J" & j).Value = shE.Range("H" & i).Value
    If IsEmpty(shE.Range("H" & i).Value) Then Sheets("Warning").Range("J" & j).Value = "Need Info"
    j = j + 3
  Next
      
  For Each ws In ActiveWorkbook.Sheets
    ws.Activate
    ws.[a1].Select
    ws.Protect
  Next ws
      
  Application.ScreenUpdating = True
  Call Tally_Name
End Sub
 
Last edited:
Upvote 0
Solution
Thank you so much! It works great! Once again it shows this is the site for help! Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
185
bh24524
B
J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
134
bobsan42
bobsan42
C
  • Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
288
craigcurtin
C
J
  • Question
VBA Help
2
Replies
17
Views
435
skillilea
S
J
  • Solved
Populating row headers based on column values
2
Replies
14
Views
728
Jonam711
J

Forum statistics

Threads
1,214,920
Messages
6,122,272
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top