imback2nite
Board Regular
- Joined
- Oct 30, 2004
- Messages
- 211
- Office Version
- 2003 or older
- Platform
- 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