drfb

Member
  • Content count

    6
  • Joined

  • Last visited

Community Reputation

0 Neutral

About drfb

Profile Information

  • OS
    Windows 7 x86
  1. Thanks for shortening these MsgBox for me.. They are working well so far so i will go through & modify my MsgBox to be as per above. I was wondering if there was a way to clean up my code a bit to make it bit neater. Hoping that it may also make it a bit small so i didn't get the Large Procedure issues. I have since worked out that the Sub Call feature i was using was slightly incorrect, so i now use ' Call Update_HW(wb2HW, wb1) ' where Update_HW is the Sub & wb2HW, wb1 are the other variables within the main Sub so it carries them across. This has resulted in me being able to spread to workload over multiple Subs which in the end has shortened the main Sub that was having issues.. Gotta love a quiet Night Shift where the Brain Activity is stimulated by learning new ways of coding. Thanks Again (gunsmokingman) ** If anyone knows of another way i can clean up the code would greatly appreciate it. All my knowledge is self taught and every day i learning something new multiple times over..
  2. Hi All, Sorry if this is in the wrong spot but i couldn't find anywhere else to place it.. I've had this Excel Database running for the past 3 years and running quite well. I have been the cleanest in coding but has worked well with over 200 employees using it. Today i came to an issue of 'Procedure Too Large'. Employees who use the Spreadsheet on Windows (Office 2003) are fine but those with MAC Office are finding that it states 'Procedure Too Large'.. Can someone help me in making a cleaner code? I have tried to 'Call Subname' etc but it doesn't work as i need to keep Setting the routines but have issues doing those. If someone could be of assistance that would be fantastic.. Thanks in advance.. HERE IS MY CODE Sub Update_with_COM()'Dim wb1 As WorksheetDim wb2 As WorkbookDim wb3 As WorksheetDim wb4 As WorksheetDim wb5 As Worksheet Set wb1 = Sheets("HOURS WORKED") ' Set wb2 --> located in next step after we disable FLICKER Set wb3 = Sheets("PAY RATES") Set wb4 = Sheets("BUDGET WORKOUT") Set wb5 = Sheets("PAY-QUERY")'Application.EnableEvents = False ' Need to DISABLE Other Macros to stop ErrorsApplication.ScreenUpdating = False ' STOP SCREEN FLICKER' MsgBox "LETS CHOOSE THE FILE WE NEED TO COPY DATA FROM" Set wb2 = Workbooks.Open(Application.GetOpenFilename) Set wb2PR = wb2.Sheets("PAY RATES") Set wb2HW = wb2.Sheets("HOURS WORKED") Set wb2BDGT = wb2.Sheets("BUDGET WORKOUT") Set wb2PQRY = wb2.Sheets("PAY-QUERY")' Dim PR_YesNo Dim mboxPR As Integer Dim strPromptPR As String strPromptPR = "Do you want to copy - PAY RATES??" mboxPR = MsgBox(strPromptPR, vbYesNo, strTitle) ' Check pressed button If mboxPR = vbYes Then PR_YesNo = "YES" Else: PR_YesNo = "NO" End If'' ###################################################################' ### PAY RATES' If PR_YesNo = "YES" Then wb2PR.Range("E1:F1").Copy ' LAST YEAR DATE wb3.Range("E1").PasteSpecial wb2PR.Range("B3:E3").Copy ' LAST YEAR $ wb3.Range("B3").PasteSpecial wb2PR.Range("E5:F5").Copy ' CURRENT DATE wb3.Range("E5").PasteSpecial wb2PR.Range("B7:E7").Copy ' CURRENT $ wb3.Range("B7").PasteSpecial wb2PR.Range("E9:F9").Copy ' LEVEL 1 DATE wb3.Range("E9").PasteSpecial wb2PR.Range("B11:E11").Copy ' LEVEL 1 $ wb3.Range("B11").PasteSpecial wb2PR.Range("E13:F13").Copy ' LEVEL 2 DATE wb3.Range("E13").PasteSpecial wb2PR.Range("B15:E15").Copy ' LEVEL 2 $ wb3.Range("B15").PasteSpecial wb2PR.Range("E17:F17").Copy ' LEVEL 3 DATE wb3.Range("E17").PasteSpecial wb2PR.Range("B19:E19").Copy ' LEVEL 3 $ wb3.Range("B19").PasteSpecial wb2PR.Range("E21:F21").Copy ' LEVEL 4 DATE wb3.Range("E21").PasteSpecial wb2PR.Range("B23:E23").Copy ' LEVEL 4 $ wb3.Range("B23").PasteSpecial' wb2PR.Range("C26:C27").Copy ' EXTRA TAX #1 wb3.Range("C26").PasteSpecial wb2PR.Range("E26").Copy ' EXTRA TAX #1 Date wb3.Range("E26").PasteSpecial wb2PR.Range("C30:C31").Copy ' EXTRA TAX #2 wb3.Range("C30").PasteSpecial wb2PR.Range("E30").Copy ' EXTRA TAX #2 Date wb3.Range("E30").PasteSpecial'' Disable Choose CENTER/TEAM as not available within v3.9' wb2PR.Range("P2:P7").Copy ' Choose CENTER/TEAM' wb3.Range("P2").PasteSpecial wb2PR.Range("O10").Copy ' Primary ESO wb3.Range("O10").PasteSpecial xlPasteValues End If'' ###################################################################' ### HOURS WORKED' Dim HW_YesNo Dim mboxHW As Integer Dim strPromptHW As String strPromptHW = "Do you want to copy - HOURS WORKED??" mboxHW = MsgBox(strPromptHW, vbYesNo, strTitle) ' Check pressed button If mboxHW = vbYes Then HW_YesNo = "YES" Else: HW_YesNo = "NO" End If' If HW_YesNo = "YES" Then wb2HW.Range("J3:K3").Copy: wb1.Range("J3").PasteSpecial xlPasteValues wb2HW.Range("M3:N3").Copy: wb1.Range("M3").PasteSpecial xlPasteValues wb2HW.Range("P3:Q3").Copy: wb1.Range("P3").PasteSpecial xlPasteValues ' RED BOXES wb2HW.Range("S3:T3").Copy: wb1.Range("S3").PasteSpecial xlPasteValues wb2HW.Range("V3:W3").Copy: wb1.Range("V3").PasteSpecial xlPasteValues wb2HW.Range("Y3:Z3").Copy: wb1.Range("Y3").PasteSpecial xlPasteValues ' wb2HW.Range("AB3:AC3").Copy: wb1.Range("AB3").PasteSpecial xlPasteValues wb2HW.Range("AE3:AF3").Copy: wb1.Range("AE3").PasteSpecial xlPasteValues wb2HW.Range("AH3:AI3").Copy: wb1.Range("AH3").PasteSpecial xlPasteValues wb2HW.Range("AK3:AL3").Copy: wb1.Range("AK3").PasteSpecial xlPasteValues ' RED BOXES wb2HW.Range("AN3:AO3").Copy: wb1.Range("AN3").PasteSpecial xlPasteValues wb2HW.Range("AQ3:AR3").Copy: wb1.Range("AQ3").PasteSpecial xlPasteValues wb2HW.Range("AT3:AU3").Copy: wb1.Range("AT3").PasteSpecial xlPasteValues wb2HW.Range("AW3:AX3").Copy: wb1.Range("AW3").PasteSpecial xlPasteValues wb2HW.Range("AZ3:BA3").Copy: wb1.Range("AZ3").PasteSpecial xlPasteValues ' wb2HW.Range("BC3:BD3").Copy: wb1.Range("BC3").PasteSpecial xlPasteValues wb2HW.Range("BF3:BG3").Copy: wb1.Range("BF3").PasteSpecial xlPasteValues wb2HW.Range("BI3:BJ3").Copy: wb1.Range("BI3").PasteSpecial xlPasteValues wb2HW.Range("BL3:BM3").Copy: wb1.Range("BL3").PasteSpecial xlPasteValues ' RED BOXES wb2HW.Range("BO3:BP3").Copy: wb1.Range("BO3").PasteSpecial xlPasteValues wb2HW.Range("BR3:BS3").Copy: wb1.Range("BR3").PasteSpecial xlPasteValues wb2HW.Range("BU3:BV3").Copy: wb1.Range("BU3").PasteSpecial xlPasteValues wb2HW.Range("BX3:BY3").Copy: wb1.Range("BX3").PasteSpecial xlPasteValues ' wb2HW.Range("CA3:CB3").Copy: wb1.Range("CA3").PasteSpecial xlPasteValues wb2HW.Range("CD3:CE3").Copy: wb1.Range("CD3").PasteSpecial xlPasteValues wb2HW.Range("CG3:CH3").Copy: wb1.Range("CG3").PasteSpecial xlPasteValues ' RED BOXES wb2HW.Range("CJ3:CK3").Copy: wb1.Range("CJ3").PasteSpecial xlPasteValues wb2HW.Range("CM3:CN3").Copy: wb1.Range("CM3").PasteSpecial xlPasteValues End If'' ### Copy Worked Shifts' Dim WS_YesNo Dim mboxWS As Integer Dim strPromptWS As String strPromptWS = "Do you want to copy - WORKED SHIFTS??" mboxWS = MsgBox(strPromptWS, vbYesNo, strTitle) ' Check pressed button If mboxWS = vbYes Then WS_YesNo = "YES" Else: WS_YesNo = "NO" End If' If WS_YesNo = "YES" Then wb2HW.Range("D9:D22").Copy ' WORKED SHIFTS wb1.Range("D9").PasteSpecial xlPasteComments: wb1.Range("D9").PasteSpecial xlPasteValues' wb2HW.Range("G9:G22").Copy wb1.Range("G9").PasteSpecial xlPasteComments: wb1.Range("G9").PasteSpecial xlPasteValues' wb2HW.Range("J9:J22").Copy ' WORKED SHIFTS wb1.Range("J9").PasteSpecial xlPasteComments: wb1.Range("J9").PasteSpecial xlPasteValues' wb2HW.Range("M9:M22").Copy wb1.Range("M9").PasteSpecial xlPasteComments: wb1.Range("M9").PasteSpecial xlPasteValues' wb2HW.Range("P9:P22").Copy ' WORKED SHIFTS wb1.Range("P9").PasteSpecial xlPasteComments: wb1.Range("P9").PasteSpecial xlPasteValues' wb2HW.Range("S9:S22").Copy wb1.Range("S9").PasteSpecial xlPasteComments: wb1.Range("S9").PasteSpecial xlPasteValues' wb2HW.Range("V9:V22").Copy ' WORKED SHIFTS wb1.Range("V9").PasteSpecial xlPasteComments: wb1.Range("V9").PasteSpecial xlPasteValues' wb2HW.Range("Y9:Y22").Copy wb1.Range("Y9").PasteSpecial xlPasteComments: wb1.Range("Y9").PasteSpecial xlPasteValues' wb2HW.Range("AB9:AB22").Copy ' WORKED SHIFTS wb1.Range("AB9").PasteSpecial xlPasteComments: wb1.Range("AB9").PasteSpecial xlPasteValues' wb2HW.Range("AE9:AE22").Copy wb1.Range("AE9").PasteSpecial xlPasteComments: wb1.Range("AE9").PasteSpecial xlPasteValues' wb2HW.Range("AH9:AH22").Copy ' WORKED SHIFTS wb1.Range("AH9").PasteSpecial xlPasteComments: wb1.Range("AH9").PasteSpecial xlPasteValues' wb2HW.Range("AK9:AK22").Copy wb1.Range("AK9").PasteSpecial xlPasteComments: wb1.Range("AK9").PasteSpecial xlPasteValues' wb2HW.Range("AN9:AN22").Copy ' WORKED SHIFTS wb1.Range("AN9").PasteSpecial xlPasteComments: wb1.Range("AN9").PasteSpecial xlPasteValues' wb2HW.Range("AQ9:AQ22").Copy wb1.Range("AQ9").PasteSpecial xlPasteComments: wb1.Range("AQ9").PasteSpecial xlPasteValues' wb2HW.Range("AT9:AT22").Copy ' WORKED SHIFTS wb1.Range("AT9").PasteSpecial xlPasteComments: wb1.Range("AT9").PasteSpecial xlPasteValues' wb2HW.Range("AW9:AW22").Copy wb1.Range("AW9").PasteSpecial xlPasteComments: wb1.Range("AW9").PasteSpecial xlPasteValues' wb2HW.Range("AZ9:AZ22").Copy ' WORKED SHIFTS wb1.Range("AZ9").PasteSpecial xlPasteComments: wb1.Range("AZ9").PasteSpecial xlPasteValues' wb2HW.Range("BC9:BC22").Copy wb1.Range("BC9").PasteSpecial xlPasteComments: wb1.Range("BC9").PasteSpecial xlPasteValues' wb2HW.Range("BF9:BF22").Copy ' WORKED SHIFTS wb1.Range("BF9").PasteSpecial xlPasteComments: wb1.Range("BF9").PasteSpecial xlPasteValues' wb2HW.Range("BI9:BI22").Copy wb1.Range("BI9").PasteSpecial xlPasteComments: wb1.Range("BI9").PasteSpecial xlPasteValues' wb2HW.Range("BL9:BL22").Copy ' WORKED SHIFTS wb1.Range("BL9").PasteSpecial xlPasteComments: wb1.Range("BL9").PasteSpecial xlPasteValues' wb2HW.Range("BO9:BO22").Copy wb1.Range("BO9").PasteSpecial xlPasteComments: wb1.Range("BO9").PasteSpecial xlPasteValues' wb2HW.Range("BR9:BR22").Copy ' WORKED SHIFTS wb1.Range("BR9").PasteSpecial xlPasteComments: wb1.Range("BR9").PasteSpecial xlPasteValues' wb2HW.Range("BU9:BU22").Copy wb1.Range("BU9").PasteSpecial xlPasteComments: wb1.Range("BU9").PasteSpecial xlPasteValues' wb2HW.Range("BX9:BX22").Copy ' WORKED SHIFTS wb1.Range("BX9").PasteSpecial xlPasteComments: wb1.Range("BX9").PasteSpecial xlPasteValues' wb2HW.Range("CA9:CA22").Copy wb1.Range("CA9").PasteSpecial xlPasteComments: wb1.Range("CA9").PasteSpecial xlPasteValues' wb2HW.Range("CD9:CD22").Copy ' WORKED SHIFTS wb1.Range("CD9").PasteSpecial xlPasteComments: wb1.Range("CD9").PasteSpecial xlPasteValues' wb2HW.Range("CG9:CG22").Copy wb1.Range("CG9").PasteSpecial xlPasteComments: wb1.Range("CG9").PasteSpecial xlPasteValues' wb2HW.Range("CJ9:CJ22").Copy ' WORKED SHIFTS wb1.Range("CJ9").PasteSpecial xlPasteComments: wb1.Range("CJ9").PasteSpecial xlPasteValues' wb2HW.Range("CM9:CM22").Copy wb1.Range("CM9").PasteSpecial xlPasteComments: wb1.Range("CM9").PasteSpecial xlPasteValues End If'' ### Copy Additional OT' Dim ADDOT_YesNo Dim mboxADDOT As Integer Dim strPromptADDOT As String strPromptADDOT = "Do you want to copy - ADDITIONAL OT??" mboxADDOT = MsgBox(strPromptADDOT, vbYesNo, strTitle) ' Check pressed button If mboxADDOT = vbYes Then ADDOT_YesNo = "YES" Else: ADDOT_YesNo = "NO" End If' If ADDOT_YesNo = "YES" Then wb2HW.Range("C23:D26").Copy ' ADDITIONAL OT wb1.Range("C23").PasteSpecial xlPasteComments: wb1.Range("C23").PasteSpecial xlPasteValues' wb2HW.Range("F23:G26").Copy wb1.Range("F23").PasteSpecial xlPasteComments: wb1.Range("F23").PasteSpecial xlPasteValues' wb2HW.Range("I23:J26").Copy ' ADDITIONAL OT wb1.Range("I23").PasteSpecial xlPasteComments: wb1.Range("I23").PasteSpecial xlPasteValues' wb2HW.Range("L23:M26").Copy wb1.Range("L23").PasteSpecial xlPasteComments: wb1.Range("L23").PasteSpecial xlPasteValues' wb2HW.Range("O23:P26").Copy ' ADDITIONAL OT wb1.Range("O23").PasteSpecial xlPasteComments: wb1.Range("O23").PasteSpecial xlPasteValues' wb2HW.Range("R23:S26").Copy wb1.Range("R23").PasteSpecial xlPasteComments: wb1.Range("R23").PasteSpecial xlPasteValues' wb2HW.Range("U23:V26").Copy ' ADDITIONAL OT wb1.Range("U23").PasteSpecial xlPasteComments: wb1.Range("U23").PasteSpecial xlPasteValues' wb2HW.Range("X23:Y26").Copy wb1.Range("X23").PasteSpecial xlPasteComments: wb1.Range("X23").PasteSpecial xlPasteValues' wb2HW.Range("AA23:AB26").Copy ' ADDITIONAL OT wb1.Range("AA23").PasteSpecial xlPasteComments: wb1.Range("AA23").PasteSpecial xlPasteValues' wb2HW.Range("AD23:AE26").Copy wb1.Range("AD23").PasteSpecial xlPasteComments: wb1.Range("AD23").PasteSpecial xlPasteValues' wb2HW.Range("AG23:AH26").Copy ' ADDITIONAL OT wb1.Range("AG23").PasteSpecial xlPasteComments: wb1.Range("AG23").PasteSpecial xlPasteValues' wb2HW.Range("AJ23:AK26").Copy wb1.Range("AJ23").PasteSpecial xlPasteComments: wb1.Range("AJ23").PasteSpecial xlPasteValues' wb2HW.Range("AM23:AN26").Copy ' ADDITIONAL OT wb1.Range("AM23").PasteSpecial xlPasteComments: wb1.Range("AM23").PasteSpecial xlPasteValues' wb2HW.Range("AP23:AQ26").Copy wb1.Range("AP23").PasteSpecial xlPasteComments: wb1.Range("AP23").PasteSpecial xlPasteValues' wb2HW.Range("AS23:AT26").Copy ' ADDITIONAL OT wb1.Range("AS23").PasteSpecial xlPasteComments: wb1.Range("AS23").PasteSpecial xlPasteValues' wb2HW.Range("AV23:AW26").Copy wb1.Range("AV23").PasteSpecial xlPasteComments: wb1.Range("AV23").PasteSpecial xlPasteValues' wb2HW.Range("AY23:AZ26").Copy ' ADDITIONAL OT wb1.Range("AY23").PasteSpecial xlPasteComments: wb1.Range("AY23").PasteSpecial xlPasteValues' wb2HW.Range("BB23:BC26").Copy wb1.Range("BB23").PasteSpecial xlPasteComments: wb1.Range("BB23").PasteSpecial xlPasteValues' wb2HW.Range("BE23:BF26").Copy ' ADDITIONAL OT wb1.Range("BE23").PasteSpecial xlPasteComments: wb1.Range("BE23").PasteSpecial xlPasteValues' wb2HW.Range("BH23:BI26").Copy wb1.Range("BH23").PasteSpecial xlPasteComments: wb1.Range("BH23").PasteSpecial xlPasteValues' wb2HW.Range("BK23:BL26").Copy ' ADDITIONAL OT wb1.Range("BK23").PasteSpecial xlPasteComments: wb1.Range("BK23").PasteSpecial xlPasteValues' wb2HW.Range("BN23:BO26").Copy wb1.Range("BN23").PasteSpecial xlPasteComments: wb1.Range("BN23").PasteSpecial xlPasteValues' wb2HW.Range("BQ23:BR26").Copy ' ADDITIONAL OT wb1.Range("BQ23").PasteSpecial xlPasteComments: wb1.Range("BQ23").PasteSpecial xlPasteValues' wb2HW.Range("BT23:BU26").Copy wb1.Range("BT23").PasteSpecial xlPasteComments: wb1.Range("BT23").PasteSpecial xlPasteValues' wb2HW.Range("BW23:BX26").Copy ' ADDITIONAL OT wb1.Range("BW23").PasteSpecial xlPasteComments: wb1.Range("BW23").PasteSpecial xlPasteValues' wb2HW.Range("BZ23:CA26").Copy wb1.Range("BZ23").PasteSpecial xlPasteComments: wb1.Range("BZ23").PasteSpecial xlPasteValues' wb2HW.Range("CC23:CD26").Copy ' ADDITIONAL OT wb1.Range("CC23").PasteSpecial xlPasteComments: wb1.Range("CC23").PasteSpecial xlPasteValues' wb2HW.Range("CF23:CG26").Copy wb1.Range("CF23").PasteSpecial xlPasteComments: wb1.Range("CF23").PasteSpecial xlPasteValues' wb2HW.Range("CI23:CJ26").Copy ' ADDITIONAL OT wb1.Range("CI23").PasteSpecial xlPasteComments: wb1.Range("CI23").PasteSpecial xlPasteValues' wb2HW.Range("CL23:CM26").Copy wb1.Range("CL23").PasteSpecial xlPasteComments: wb1.Range("CL23").PasteSpecial xlPasteValues'' ### Copy Other Shifts / OT' wb2HW.Range("C27:CN30").Copy ' OTHER SHIFTS / OT wb1.Range("C27").PasteSpecial xlPasteComments: wb1.Range("C27").PasteSpecial xlPasteValues End If'' ### Copy HDA' Dim HDA_YesNo Dim mboxHDA As Integer Dim strPromptHDA As String strPromptHDA = "Do you want to copy - HDA??" mboxHDA = MsgBox(strPromptHDA, vbYesNo, strTitle) ' Check pressed button If mboxHDA = vbYes Then HDA_YesNo = "YES" Else: HDA_YesNo = "NO" End If' If HDA_YesNo = "YES" Then wb2HW.Range("C32:D32").Copy ' HDA wb1.Range("C32").PasteSpecial xlPasteComments: wb1.Range("C32").PasteSpecial xlPasteValues' wb2HW.Range("F32:G32").Copy wb1.Range("F32").PasteSpecial xlPasteComments: wb1.Range("F32").PasteSpecial xlPasteValues' wb2HW.Range("I32:J32").Copy ' HDA wb1.Range("I32").PasteSpecial xlPasteComments: wb1.Range("I32").PasteSpecial xlPasteValues' wb2HW.Range("L32:M32").Copy wb1.Range("L32").PasteSpecial xlPasteComments: wb1.Range("L32").PasteSpecial xlPasteValues' wb2HW.Range("O32:P32").Copy ' HDA wb1.Range("O32").PasteSpecial xlPasteComments: wb1.Range("O32").PasteSpecial xlPasteValues' wb2HW.Range("R32:S32").Copy wb1.Range("R32").PasteSpecial xlPasteComments: wb1.Range("R32").PasteSpecial xlPasteValues' wb2HW.Range("U32:V32").Copy ' HDA wb1.Range("U32").PasteSpecial xlPasteComments: wb1.Range("U32").PasteSpecial xlPasteValues' wb2HW.Range("X32:Y32").Copy wb1.Range("X32").PasteSpecial xlPasteComments: wb1.Range("X32").PasteSpecial xlPasteValues' wb2HW.Range("AA32:AB32").Copy ' HDA wb1.Range("AA32").PasteSpecial xlPasteComments: wb1.Range("AA32").PasteSpecial xlPasteValues' wb2HW.Range("AD32:AE32").Copy wb1.Range("AD32").PasteSpecial xlPasteComments: wb1.Range("AD32").PasteSpecial xlPasteValues' wb2HW.Range("AG32:AH32").Copy ' HDA wb1.Range("AG32").PasteSpecial xlPasteComments: wb1.Range("AG32").PasteSpecial xlPasteValues' wb2HW.Range("AJ32:AK32").Copy wb1.Range("AJ32").PasteSpecial xlPasteComments: wb1.Range("AJ32").PasteSpecial xlPasteValues' wb2HW.Range("AM32:AN32").Copy ' HDA wb1.Range("AM32").PasteSpecial xlPasteComments: wb1.Range("AM32").PasteSpecial xlPasteValues' wb2HW.Range("AP32:AQ32").Copy wb1.Range("AP32").PasteSpecial xlPasteComments: wb1.Range("AP32").PasteSpecial xlPasteValues' wb2HW.Range("AS32:AT32").Copy ' HDA wb1.Range("AS32").PasteSpecial xlPasteComments: wb1.Range("AS32").PasteSpecial xlPasteValues' wb2HW.Range("AV32:AW32").Copy wb1.Range("AV32").PasteSpecial xlPasteComments: wb1.Range("AV32").PasteSpecial xlPasteValues' wb2HW.Range("AY32:AZ32").Copy ' HDA wb1.Range("AY32").PasteSpecial xlPasteComments: wb1.Range("AY32").PasteSpecial xlPasteValues' wb2HW.Range("BB32:BC32").Copy wb1.Range("BB32").PasteSpecial xlPasteComments: wb1.Range("BB32").PasteSpecial xlPasteValues' wb2HW.Range("BE32:BF32").Copy ' HDA wb1.Range("BE32").PasteSpecial xlPasteComments: wb1.Range("BE32").PasteSpecial xlPasteValues' wb2HW.Range("BH32:BI32").Copy wb1.Range("BH32").PasteSpecial xlPasteComments: wb1.Range("BH32").PasteSpecial xlPasteValues' wb2HW.Range("BK32:BL32").Copy ' HDA wb1.Range("BK32").PasteSpecial xlPasteComments: wb1.Range("BK32").PasteSpecial xlPasteValues' wb2HW.Range("BN32:BO32").Copy wb1.Range("BN32").PasteSpecial xlPasteComments: wb1.Range("BN32").PasteSpecial xlPasteValues' wb2HW.Range("BQ32:BR32").Copy ' HDA wb1.Range("BQ32").PasteSpecial xlPasteComments: wb1.Range("BQ32").PasteSpecial xlPasteValues' wb2HW.Range("BT32:BU32").Copy wb1.Range("BT32").PasteSpecial xlPasteComments: wb1.Range("BT32").PasteSpecial xlPasteValues' wb2HW.Range("BW32:BX32").Copy ' HDA wb1.Range("BW32").PasteSpecial xlPasteComments: wb1.Range("BW32").PasteSpecial xlPasteValues' wb2HW.Range("BZ32:CA32").Copy wb1.Range("BZ32").PasteSpecial xlPasteComments: wb1.Range("BZ32").PasteSpecial xlPasteValues' wb2HW.Range("CC32:CD32").Copy ' HDA wb1.Range("CC32").PasteSpecial xlPasteComments: wb1.Range("CC32").PasteSpecial xlPasteValues' wb2HW.Range("CF32:CG32").Copy wb1.Range("CF32").PasteSpecial xlPasteComments: wb1.Range("CF32").PasteSpecial xlPasteValues' wb2HW.Range("CI32:CJ32").Copy ' HDA wb1.Range("CI32").PasteSpecial xlPasteComments: wb1.Range("CI32").PasteSpecial xlPasteValues' wb2HW.Range("CL32:CM32").Copy wb1.Range("CL32").PasteSpecial xlPasteComments: wb1.Range("CL32").PasteSpecial xlPasteValues End If'' ### Copy Other Allowances' Dim ALLOW_YesNo Dim mboxALLOW As Integer Dim strPromptALLOW As String strPromptALLOW = "Do you want to copy - ALLOWANCES??" mboxALLOW = MsgBox(strPromptALLOW, vbYesNo, strTitle) ' Check pressed button If mboxALLOW = vbYes Then wb2HW.Range("C34:CN34").Copy ' OTHER ALLOWANCES wb1.Range("C34").PasteSpecial xlPasteComments: wb1.Range("C34").PasteSpecial xlPasteValues Else: ALLOW_YesNo = "NO" End If'' ### Copy Annual Leave & Leave With Out Pay' Dim LWOP_YesNo Dim mboxLWOP As Integer Dim strPromptLWOP As String strPromptLWOP = "Do you want to copy - ANNUAL LEAVE / LWOP??" mboxLWOP = MsgBox(strPromptLWOP, vbYesNo, strTitle) ' Check pressed button If mboxLWOP = vbYes Then LWOP_YesNo = "YES" Else: LWOP_YesNo = "NO" End If' If LWOP_YesNo = "YES" Then wb2HW.Range("C37:C38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("C37").PasteSpecial xlPasteValues' wb2HW.Range("F37:F38").Copy wb1.Range("F37").PasteSpecial xlPasteValues' wb2HW.Range("I37:I38").Copy wb1.Range("I37").PasteSpecial xlPasteValues' wb2HW.Range("L37:L38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("L37").PasteSpecial xlPasteValues' wb2HW.Range("O37:O38").Copy wb1.Range("O37").PasteSpecial xlPasteValues' wb2HW.Range("R37:R38").Copy wb1.Range("R37").PasteSpecial xlPasteValues' wb2HW.Range("U37:U38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("U37").PasteSpecial xlPasteValues' wb2HW.Range("X37:X38").Copy wb1.Range("X37").PasteSpecial xlPasteValues' wb2HW.Range("AA37:AA38").Copy wb1.Range("AA37").PasteSpecial xlPasteValues' wb2HW.Range("AD37:AD38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("AD37").PasteSpecial xlPasteValues' wb2HW.Range("AG37:AG38").Copy wb1.Range("AG37").PasteSpecial xlPasteValues' wb2HW.Range("AJ37:AJ38").Copy wb1.Range("AJ37").PasteSpecial xlPasteValues' wb2HW.Range("AM37:AM38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("AM37").PasteSpecial xlPasteValues' wb2HW.Range("AP37:AP38").Copy wb1.Range("AP37").PasteSpecial xlPasteValues' wb2HW.Range("AS37:AS38").Copy wb1.Range("AS37").PasteSpecial xlPasteValues' wb2HW.Range("AV37:AV38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("AV37").PasteSpecial xlPasteValues' wb2HW.Range("AY37:AY38").Copy wb1.Range("AY37").PasteSpecial xlPasteValues' wb2HW.Range("BB37:BB38").Copy wb1.Range("BB37").PasteSpecial xlPasteValues' wb2HW.Range("BE37:BE38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("BE37").PasteSpecial xlPasteValues' wb2HW.Range("BH37:BH38").Copy wb1.Range("BH37").PasteSpecial xlPasteValues' wb2HW.Range("BK37:BK38").Copy wb1.Range("BK37").PasteSpecial xlPasteValues' wb2HW.Range("BN37:BN38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("BN37").PasteSpecial xlPasteValues' wb2HW.Range("BQ37:BQ38").Copy wb1.Range("BQ37").PasteSpecial xlPasteValues' wb2HW.Range("BT37:BT38").Copy wb1.Range("BT37").PasteSpecial xlPasteValues' wb2HW.Range("BW37:BW38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("BW37").PasteSpecial xlPasteValues' wb2HW.Range("BZ37:BZ38").Copy wb1.Range("BZ37").PasteSpecial xlPasteValues' wb2HW.Range("CC37:CC38").Copy wb1.Range("CC37").PasteSpecial xlPasteValues' wb2HW.Range("CF37:CF38").Copy ' LEAVE ANNUAL/WITH OUT PAY wb1.Range("CF37").PasteSpecial xlPasteValues' wb2HW.Range("CI37:CI38").Copy wb1.Range("CI37").PasteSpecial xlPasteValues' wb2HW.Range("CL37:CL38").Copy wb1.Range("CL37").PasteSpecial xlPasteValues End If'' ### Copy Pre-Tax Deductions' Dim PRETAX_YesNo Dim mboxPRETAX As Integer Dim strPromptPRETAX As String strPromptPRETAX = "Do you want to copy - PRE-TAX DEDUCTIONS??" mboxPRETAX = MsgBox(strPromptPRETAX, vbYesNo, strTitle) ' Check pressed button If mboxPRETAX = vbYes Then PRETAX_YesNo = "YES" Else: PRETAX_YesNo = "NO" End If' If PRETAX_YesNo = "YES" Then wb2HW.Range("E40").Copy ' PRE-TAX DEDUCTION wb1.Range("E40").PasteSpecial xlPasteValues' wb2HW.Range("H40").Copy wb1.Range("H40").PasteSpecial xlPasteValues' wb2HW.Range("K40").Copy wb1.Range("K40").PasteSpecial xlPasteValues' wb2HW.Range("N40").Copy ' PRE-TAX DEDUCTION wb1.Range("N40").PasteSpecial xlPasteValues' wb2HW.Range("Q40").Copy wb1.Range("Q40").PasteSpecial xlPasteValues' wb2HW.Range("T40").Copy wb1.Range("T40").PasteSpecial xlPasteValues' wb2HW.Range("W40").Copy ' PRE-TAX DEDUCTION wb1.Range("W40").PasteSpecial xlPasteValues' wb2HW.Range("Z40").Copy wb1.Range("Z40").PasteSpecial xlPasteValues' wb2HW.Range("AC40").Copy wb1.Range("AC40").PasteSpecial xlPasteValues' wb2HW.Range("AF40").Copy ' PRE-TAX DEDUCTION wb1.Range("AF40").PasteSpecial xlPasteValues' wb2HW.Range("AI40").Copy wb1.Range("AI40").PasteSpecial xlPasteValues' wb2HW.Range("AL40").Copy wb1.Range("AL40").PasteSpecial xlPasteValues' wb2HW.Range("AO40").Copy ' PRE-TAX DEDUCTION wb1.Range("AO40").PasteSpecial xlPasteValues' wb2HW.Range("AR40").Copy wb1.Range("AR40").PasteSpecial xlPasteValues' wb2HW.Range("AU40").Copy wb1.Range("AU40").PasteSpecial xlPasteValues' wb2HW.Range("AX40").Copy ' PRE-TAX DEDUCTION wb1.Range("AX40").PasteSpecial xlPasteValues' wb2HW.Range("BA40").Copy wb1.Range("BA40").PasteSpecial xlPasteValues' wb2HW.Range("BD40").Copy wb1.Range("BD40").PasteSpecial xlPasteValues' wb2HW.Range("BG40").Copy ' PRE-TAX DEDUCTION wb1.Range("BG40").PasteSpecial xlPasteValues' wb2HW.Range("BJ40").Copy wb1.Range("BJ40").PasteSpecial xlPasteValues' wb2HW.Range("BM40").Copy wb1.Range("BM40").PasteSpecial xlPasteValues' wb2HW.Range("BP40").Copy ' PRE-TAX DEDUCTION wb1.Range("BP40").PasteSpecial xlPasteValues' wb2HW.Range("BS40").Copy wb1.Range("BS40").PasteSpecial xlPasteValues' wb2HW.Range("BV40").Copy wb1.Range("BV40").PasteSpecial xlPasteValues' wb2HW.Range("BY40").Copy ' PRE-TAX DEDUCTION wb1.Range("BY40").PasteSpecial xlPasteValues' wb2HW.Range("CB40").Copy wb1.Range("CB40").PasteSpecial xlPasteValues' wb2HW.Range("CE40").Copy wb1.Range("CE40").PasteSpecial xlPasteValues' wb2HW.Range("CH40").Copy ' PRE-TAX DEDUCTION wb1.Range("CH40").PasteSpecial xlPasteValues' wb2HW.Range("CK40").Copy wb1.Range("CK40").PasteSpecial xlPasteValues' wb2HW.Range("CN40").Copy wb1.Range("CN40").PasteSpecial xlPasteValues End If'' ### Copy Post-Tax Deductions' Dim POSTAX_YesNo Dim mboxPOSTAX As Integer Dim strPromptPOSTAX As String strPromptPOSTAX = "Do you want to copy - POST-TAX DEDUCTIONS??" mboxPOSTAX = MsgBox(strPromptPOSTAX, vbYesNo, strTitle) ' Check pressed button If mboxPOSTAX = vbYes Then POSTAX_YesNo = "YES" Else: POSTAX_YesNo = "NO" End If' If POSTAX_YesNo = "YES" Then wb2HW.Range("E43").Copy ' POST-TAX DEDUCTION wb1.Range("E43").PasteSpecial xlPasteValues' wb2HW.Range("H43").Copy wb1.Range("H43").PasteSpecial xlPasteValues' wb2HW.Range("K43").Copy wb1.Range("K43").PasteSpecial xlPasteValues' wb2HW.Range("N43").Copy ' POST-TAX DEDUCTION wb1.Range("N43").PasteSpecial xlPasteValues' wb2HW.Range("Q43").Copy wb1.Range("Q43").PasteSpecial xlPasteValues' wb2HW.Range("T43").Copy wb1.Range("T43").PasteSpecial xlPasteValues' wb2HW.Range("W43").Copy ' POST-TAX DEDUCTION wb1.Range("W43").PasteSpecial xlPasteValues' wb2HW.Range("Z43").Copy wb1.Range("Z43").PasteSpecial xlPasteValues' wb2HW.Range("AC43").Copy wb1.Range("AC43").PasteSpecial xlPasteValues' wb2HW.Range("AF43").Copy ' POST-TAX DEDUCTION wb1.Range("AF43").PasteSpecial xlPasteValues' wb2HW.Range("AI43").Copy wb1.Range("AI43").PasteSpecial xlPasteValues' wb2HW.Range("AL43").Copy wb1.Range("AL43").PasteSpecial xlPasteValues' wb2HW.Range("AO43").Copy ' POST-TAX DEDUCTION wb1.Range("AO43").PasteSpecial xlPasteValues' wb2HW.Range("AR43").Copy wb1.Range("AR43").PasteSpecial xlPasteValues' wb2HW.Range("AU43").Copy wb1.Range("AU43").PasteSpecial xlPasteValues' wb2HW.Range("AX43").Copy ' POST-TAX DEDUCTION wb1.Range("AX43").PasteSpecial xlPasteValues' wb2HW.Range("BA43").Copy wb1.Range("BA43").PasteSpecial xlPasteValues' wb2HW.Range("BD43").Copy wb1.Range("BD43").PasteSpecial xlPasteValues' wb2HW.Range("BG43").Copy ' POST-TAX DEDUCTION wb1.Range("BG43").PasteSpecial xlPasteValues' wb2HW.Range("BJ43").Copy wb1.Range("BJ43").PasteSpecial xlPasteValues' wb2HW.Range("BM43").Copy wb1.Range("BM43").PasteSpecial xlPasteValues' wb2HW.Range("BP43").Copy ' POST-TAX DEDUCTION wb1.Range("BP43").PasteSpecial xlPasteValues' wb2HW.Range("BS43").Copy wb1.Range("BS43").PasteSpecial xlPasteValues' wb2HW.Range("BV43").Copy wb1.Range("BV43").PasteSpecial xlPasteValues' wb2HW.Range("BY43").Copy ' POST-TAX DEDUCTION wb1.Range("BY43").PasteSpecial xlPasteValues' wb2HW.Range("CB43").Copy wb1.Range("CB43").PasteSpecial xlPasteValues' wb2HW.Range("CE43").Copy wb1.Range("CE43").PasteSpecial xlPasteValues' wb2HW.Range("CH43").Copy ' POST-TAX DEDUCTION wb1.Range("CH43").PasteSpecial xlPasteValues' wb2HW.Range("CK43").Copy wb1.Range("CK43").PasteSpecial xlPasteValues' wb2HW.Range("CN43").Copy wb1.Range("CN43").PasteSpecial xlPasteValues End If'' ### Copy LAST YEAR Annual Leave' wb2HW.Range("E46").Copy wb1.Range("E46").PasteSpecial xlPasteValues'' ### Copy DEFAULT YTD' wb2HW.Range("A59:A63").Copy wb1.Range("A59").PasteSpecial xlPasteValues'' ###################################################################' ### PAY QUERY' Dim PQRY_YesNo Dim mboxPQRY As Integer Dim strPromptPQRY As String strPromptPQRY = "Do you want to copy - PAY QUERY DATA??" mboxPQRY = MsgBox(strPromptPQRY, vbYesNo, strTitle) ' Check pressed button If mboxPQRY = vbYes Then'' ### Copy Worked Shifts' wb2PQRY.Range("C10:D14").Copy ' WORKED SHIFTS wb5.Range("C10").PasteSpecial xlPasteComments: wb5.Range("C10").PasteSpecial xlPasteValues' wb2PQRY.Range("F10:G14").Copy wb5.Range("F10").PasteSpecial xlPasteComments: wb5.Range("F10").PasteSpecial xlPasteValues' wb2PQRY.Range("I10:J14").Copy ' WORKED SHIFTS wb5.Range("I10").PasteSpecial xlPasteComments: wb5.Range("I10").PasteSpecial xlPasteValues' wb2PQRY.Range("L10:M14").Copy wb5.Range("L10").PasteSpecial xlPasteComments: wb5.Range("L10").PasteSpecial xlPasteValues' wb2PQRY.Range("O10:P14").Copy ' WORKED SHIFTS wb5.Range("O10").PasteSpecial xlPasteComments: wb5.Range("O10").PasteSpecial xlPasteValues' wb2PQRY.Range("R10:S14").Copy wb5.Range("R10").PasteSpecial xlPasteComments: wb5.Range("R10").PasteSpecial xlPasteValues' wb2PQRY.Range("U10:V14").Copy ' WORKED SHIFTS wb5.Range("U10").PasteSpecial xlPasteComments: wb5.Range("U10").PasteSpecial xlPasteValues' wb2PQRY.Range("X10:Y14").Copy wb5.Range("X10").PasteSpecial xlPasteComments: wb5.Range("X10").PasteSpecial xlPasteValues' wb2PQRY.Range("AA10:AB14").Copy ' WORKED SHIFTS wb5.Range("AA10").PasteSpecial xlPasteComments: wb5.Range("AA10").PasteSpecial xlPasteValues' wb2PQRY.Range("AD10:AE14").Copy wb5.Range("AD10").PasteSpecial xlPasteComments: wb5.Range("AD10").PasteSpecial xlPasteValues' wb2PQRY.Range("AG10:AH14").Copy ' WORKED SHIFTS wb5.Range("AG10").PasteSpecial xlPasteComments: wb5.Range("AG10").PasteSpecial xlPasteValues' wb2PQRY.Range("AJ10:AK14").Copy wb5.Range("AJ10").PasteSpecial xlPasteComments: wb5.Range("AJ10").PasteSpecial xlPasteValues' wb2PQRY.Range("AM10:AN14").Copy ' WORKED SHIFTS wb5.Range("AM10").PasteSpecial xlPasteComments: wb5.Range("AM10").PasteSpecial xlPasteValues' wb2PQRY.Range("AP10:AQ14").Copy wb5.Range("AP10").PasteSpecial xlPasteComments: wb5.Range("AP10").PasteSpecial xlPasteValues' wb2PQRY.Range("AS10:AT14").Copy ' WORKED SHIFTS wb5.Range("AS10").PasteSpecial xlPasteComments: wb5.Range("AS10").PasteSpecial xlPasteValues' wb2PQRY.Range("AV10:AW14").Copy wb5.Range("AV10").PasteSpecial xlPasteComments: wb5.Range("AV10").PasteSpecial xlPasteValues' wb2PQRY.Range("AY10:AZ14").Copy ' WORKED SHIFTS wb5.Range("AY10").PasteSpecial xlPasteComments: wb5.Range("AY10").PasteSpecial xlPasteValues' wb2PQRY.Range("BB10:BC14").Copy wb5.Range("BB10").PasteSpecial xlPasteComments: wb5.Range("BB10").PasteSpecial xlPasteValues' wb2PQRY.Range("BE10:BF14").Copy ' WORKED SHIFTS wb5.Range("BE10").PasteSpecial xlPasteComments: wb5.Range("BE10").PasteSpecial xlPasteValues' wb2PQRY.Range("BH10:BI14").Copy wb5.Range("BH10").PasteSpecial xlPasteComments: wb5.Range("BH10").PasteSpecial xlPasteValues' wb2PQRY.Range("BK10:BL14").Copy ' WORKED SHIFTS wb5.Range("BK10").PasteSpecial xlPasteComments: wb5.Range("BK10").PasteSpecial xlPasteValues' wb2PQRY.Range("BN10:BO14").Copy wb5.Range("BN10").PasteSpecial xlPasteComments: wb5.Range("BN10").PasteSpecial xlPasteValues' wb2PQRY.Range("BQ10:BR14").Copy ' WORKED SHIFTS wb5.Range("BQ10").PasteSpecial xlPasteComments: wb5.Range("BQ10").PasteSpecial xlPasteValues' wb2PQRY.Range("BT10:BU14").Copy wb5.Range("BT10").PasteSpecial xlPasteComments: wb5.Range("BT10").PasteSpecial xlPasteValues' wb2PQRY.Range("BW10:BX14").Copy ' WORKED SHIFTS wb5.Range("BW10").PasteSpecial xlPasteComments: wb5.Range("BW10").PasteSpecial xlPasteValues' wb2PQRY.Range("BZ10:CA14").Copy wb5.Range("BZ10").PasteSpecial xlPasteComments: wb5.Range("BZ10").PasteSpecial xlPasteValues' wb2PQRY.Range("CC10:CD14").Copy ' WORKED SHIFTS wb5.Range("CC10").PasteSpecial xlPasteComments: wb5.Range("CC10").PasteSpecial xlPasteValues' wb2PQRY.Range("CF10:CG14").Copy wb5.Range("CF10").PasteSpecial xlPasteComments: wb5.Range("CF10").PasteSpecial xlPasteValues' wb2PQRY.Range("CI10:CJ14").Copy ' WORKED SHIFTS wb5.Range("CI10").PasteSpecial xlPasteComments: wb5.Range("CI10").PasteSpecial xlPasteValues' wb2PQRY.Range("CL10:CM14").Copy wb5.Range("CL10").PasteSpecial xlPasteComments: wb5.Range("CL10").PasteSpecial xlPasteValues'' ### Copy Additional OT' wb2PQRY.Range("C15:D16").Copy ' ADDITIONAL OT wb5.Range("C15").PasteSpecial xlPasteComments: wb5.Range("C15").PasteSpecial xlPasteValues' wb2PQRY.Range("F15:G16").Copy wb5.Range("F15").PasteSpecial xlPasteComments: wb5.Range("F15").PasteSpecial xlPasteValues' wb2PQRY.Range("I15:J16").Copy ' ADDITIONAL OT wb5.Range("I15").PasteSpecial xlPasteComments: wb5.Range("I15").PasteSpecial xlPasteValues' wb2PQRY.Range("L15:M16").Copy wb5.Range("L15").PasteSpecial xlPasteComments: wb5.Range("L15").PasteSpecial xlPasteValues' wb2PQRY.Range("O15:P16").Copy ' ADDITIONAL OT wb5.Range("O15").PasteSpecial xlPasteComments: wb5.Range("O15").PasteSpecial xlPasteValues' wb2PQRY.Range("R15:S16").Copy wb5.Range("R15").PasteSpecial xlPasteComments: wb5.Range("R15").PasteSpecial xlPasteValues' wb2PQRY.Range("U15:V16").Copy ' ADDITIONAL OT wb5.Range("U15").PasteSpecial xlPasteComments: wb5.Range("U15").PasteSpecial xlPasteValues' wb2PQRY.Range("X15:Y16").Copy wb5.Range("X15").PasteSpecial xlPasteComments: wb5.Range("X15").PasteSpecial xlPasteValues' wb2PQRY.Range("AA15:AB16").Copy ' ADDITIONAL OT wb5.Range("AA15").PasteSpecial xlPasteComments: wb5.Range("AA15").PasteSpecial xlPasteValues' wb2PQRY.Range("AD15:AE16").Copy wb5.Range("AD15").PasteSpecial xlPasteComments: wb5.Range("AD15").PasteSpecial xlPasteValues' wb2PQRY.Range("AG15:AH16").Copy ' ADDITIONAL OT wb5.Range("AG15").PasteSpecial xlPasteComments: wb5.Range("AG15").PasteSpecial xlPasteValues' wb2PQRY.Range("AJ15:AK16").Copy wb5.Range("AJ15").PasteSpecial xlPasteComments: wb5.Range("AJ15").PasteSpecial xlPasteValues' wb2PQRY.Range("AM15:AN16").Copy ' ADDITIONAL OT wb5.Range("AM15").PasteSpecial xlPasteComments: wb5.Range("AM15").PasteSpecial xlPasteValues' wb2PQRY.Range("AP15:AQ16").Copy wb5.Range("AP15").PasteSpecial xlPasteComments: wb5.Range("AP15").PasteSpecial xlPasteValues' wb2PQRY.Range("AS15:AT16").Copy ' ADDITIONAL OT wb5.Range("AS15").PasteSpecial xlPasteComments: wb5.Range("AS15").PasteSpecial xlPasteValues' wb2PQRY.Range("AV15:AW16").Copy wb5.Range("AV15").PasteSpecial xlPasteComments: wb5.Range("AV15").PasteSpecial xlPasteValues' wb2PQRY.Range("AY15:AZ16").Copy ' ADDITIONAL OT wb5.Range("AY15").PasteSpecial xlPasteComments: wb5.Range("AY15").PasteSpecial xlPasteValues' wb2PQRY.Range("BB15:BC16").Copy wb5.Range("BB15").PasteSpecial xlPasteComments: wb5.Range("BB15").PasteSpecial xlPasteValues' wb2PQRY.Range("BE15:BF16").Copy ' ADDITIONAL OT wb5.Range("BE15").PasteSpecial xlPasteComments: wb5.Range("BE15").PasteSpecial xlPasteValues' wb2PQRY.Range("BH15:BI16").Copy wb5.Range("BH15").PasteSpecial xlPasteComments: wb5.Range("BH15").PasteSpecial xlPasteValues' wb2PQRY.Range("BK15:BL16").Copy ' ADDITIONAL OT wb5.Range("BK15").PasteSpecial xlPasteComments: wb5.Range("BK15").PasteSpecial xlPasteValues' wb2PQRY.Range("BN15:BO16").Copy wb5.Range("BN15").PasteSpecial xlPasteComments: wb5.Range("BN15").PasteSpecial xlPasteValues' wb2PQRY.Range("BQ15:BR16").Copy ' ADDITIONAL OT wb5.Range("BQ15").PasteSpecial xlPasteComments: wb5.Range("BQ15").PasteSpecial xlPasteValues' wb2PQRY.Range("BT15:BU16").Copy wb5.Range("BT15").PasteSpecial xlPasteComments: wb5.Range("BT15").PasteSpecial xlPasteValues' wb2PQRY.Range("BW15:BX16").Copy ' ADDITIONAL OT wb5.Range("BW15").PasteSpecial xlPasteComments: wb5.Range("BW15").PasteSpecial xlPasteValues' wb2PQRY.Range("BZ15:CA16").Copy wb5.Range("BZ15").PasteSpecial xlPasteComments: wb5.Range("BZ15").PasteSpecial xlPasteValues' wb2PQRY.Range("CC15:CD16").Copy ' ADDITIONAL OT wb5.Range("CC15").PasteSpecial xlPasteComments: wb5.Range("CC15").PasteSpecial xlPasteValues' wb2PQRY.Range("CF15:CG16").Copy wb5.Range("CF15").PasteSpecial xlPasteComments: wb5.Range("CF15").PasteSpecial xlPasteValues' wb2PQRY.Range("CI15:CJ16").Copy ' ADDITIONAL OT wb5.Range("CI15").PasteSpecial xlPasteComments: wb5.Range("CI15").PasteSpecial xlPasteValues' wb2PQRY.Range("CL15:CM16").Copy wb5.Range("CL15").PasteSpecial xlPasteComments: wb5.Range("CL15").PasteSpecial xlPasteValues'' ### Copy Other Shifts / OT' wb2PQRY.Range("C17:CN18").Copy ' OTHER SHIFTS / OT wb5.Range("C17").PasteSpecial xlPasteComments: wb5.Range("C17").PasteSpecial xlPasteValues'' ### Copy HDA' wb2PQRY.Range("C20:D20").Copy ' HDA wb5.Range("C20").PasteSpecial xlPasteComments: wb5.Range("C20").PasteSpecial xlPasteValues' wb2PQRY.Range("F20:G20").Copy wb5.Range("F20").PasteSpecial xlPasteComments: wb5.Range("F20").PasteSpecial xlPasteValues' wb2PQRY.Range("I20:J20").Copy ' HDA wb5.Range("I20").PasteSpecial xlPasteComments: wb5.Range("I20").PasteSpecial xlPasteValues' wb2PQRY.Range("L20:M20").Copy wb5.Range("L20").PasteSpecial xlPasteComments: wb5.Range("L20").PasteSpecial xlPasteValues' wb2PQRY.Range("O20:P20").Copy ' HDA wb5.Range("O20").PasteSpecial xlPasteComments: wb5.Range("O20").PasteSpecial xlPasteValues' wb2PQRY.Range("R20:S20").Copy wb5.Range("R20").PasteSpecial xlPasteComments: wb5.Range("R20").PasteSpecial xlPasteValues' wb2PQRY.Range("U20:V20").Copy ' HDA wb5.Range("U20").PasteSpecial xlPasteComments: wb5.Range("U20").PasteSpecial xlPasteValues' wb2PQRY.Range("X20:Y20").Copy wb5.Range("X20").PasteSpecial xlPasteComments: wb5.Range("X20").PasteSpecial xlPasteValues' wb2PQRY.Range("AA20:AB20").Copy ' HDA wb5.Range("AA20").PasteSpecial xlPasteComments: wb5.Range("AA20").PasteSpecial xlPasteValues' wb2PQRY.Range("AD20:AE20").Copy wb5.Range("AD20").PasteSpecial xlPasteComments: wb5.Range("AD20").PasteSpecial xlPasteValues' wb2PQRY.Range("AG20:AH20").Copy ' HDA wb5.Range("AG20").PasteSpecial xlPasteComments: wb5.Range("AG20").PasteSpecial xlPasteValues' wb2PQRY.Range("AJ20:AK20").Copy wb5.Range("AJ20").PasteSpecial xlPasteComments: wb5.Range("AJ20").PasteSpecial xlPasteValues' wb2PQRY.Range("AM20:AN20").Copy ' HDA wb5.Range("AM20").PasteSpecial xlPasteComments: wb5.Range("AM20").PasteSpecial xlPasteValues' wb2PQRY.Range("AP20:AQ20").Copy wb5.Range("AP20").PasteSpecial xlPasteComments: wb5.Range("AP20").PasteSpecial xlPasteValues' wb2PQRY.Range("AS20:AT20").Copy ' HDA wb5.Range("AS20").PasteSpecial xlPasteComments: wb5.Range("AS20").PasteSpecial xlPasteValues' wb2PQRY.Range("AV20:AW20").Copy wb5.Range("AV20").PasteSpecial xlPasteComments: wb5.Range("AV20").PasteSpecial xlPasteValues' wb2PQRY.Range("AY20:AZ20").Copy ' HDA wb5.Range("AY20").PasteSpecial xlPasteComments: wb5.Range("AY20").PasteSpecial xlPasteValues' wb2PQRY.Range("BB20:BC20").Copy wb5.Range("BB20").PasteSpecial xlPasteComments: wb5.Range("BB20").PasteSpecial xlPasteValues' wb2PQRY.Range("BE20:BF20").Copy ' HDA wb5.Range("BE20").PasteSpecial xlPasteComments: wb5.Range("BE20").PasteSpecial xlPasteValues' wb2PQRY.Range("BH20:BI20").Copy wb5.Range("BH20").PasteSpecial xlPasteComments: wb5.Range("BH20").PasteSpecial xlPasteValues' wb2PQRY.Range("BK20:BL20").Copy ' HDA wb5.Range("BK20").PasteSpecial xlPasteComments: wb5.Range("BK20").PasteSpecial xlPasteValues' wb2PQRY.Range("BN20:BO20").Copy wb5.Range("BN20").PasteSpecial xlPasteComments: wb5.Range("BN20").PasteSpecial xlPasteValues' wb2PQRY.Range("BQ20:BR20").Copy ' HDA wb5.Range("BQ20").PasteSpecial xlPasteComments: wb5.Range("BQ20").PasteSpecial xlPasteValues' wb2PQRY.Range("BT20:BU20").Copy wb5.Range("BT20").PasteSpecial xlPasteComments: wb5.Range("BT20").PasteSpecial xlPasteValues' wb2PQRY.Range("BW20:BX20").Copy ' HDA wb5.Range("BW20").PasteSpecial xlPasteComments: wb5.Range("BW20").PasteSpecial xlPasteValues' wb2PQRY.Range("BZ20:CA20").Copy wb5.Range("BZ20").PasteSpecial xlPasteComments: wb5.Range("BZ20").PasteSpecial xlPasteValues' wb2PQRY.Range("CC20:CD20").Copy ' HDA wb5.Range("CC20").PasteSpecial xlPasteComments: wb5.Range("CC20").PasteSpecial xlPasteValues' wb2PQRY.Range("CF20:CG20").Copy wb5.Range("CF20").PasteSpecial xlPasteComments: wb5.Range("CF20").PasteSpecial xlPasteValues' wb2PQRY.Range("CI20:CJ20").Copy ' HDA wb5.Range("CI20").PasteSpecial xlPasteComments: wb5.Range("CI20").PasteSpecial xlPasteValues' wb2PQRY.Range("CL20:CM20").Copy wb5.Range("CL20").PasteSpecial xlPasteComments: wb5.Range("CL20").PasteSpecial xlPasteValues'' ### Copy Other Allowances' wb2PQRY.Range("C22:CN22").Copy ' OTHER ALLOWANCES wb5.Range("C22").PasteSpecial xlPasteComments: wb5.Range("C22").PasteSpecial xlPasteValues'' ### Copy Annual Leave & Leave With Out Pay' wb2PQRY.Range("C25:C26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("C25").PasteSpecial xlPasteValues' wb2PQRY.Range("F25:F26").Copy wb5.Range("F25").PasteSpecial xlPasteValues' wb2PQRY.Range("I25:I26").Copy wb5.Range("I25").PasteSpecial xlPasteValues' wb2PQRY.Range("L25:L26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("L25").PasteSpecial xlPasteValues' wb2PQRY.Range("O25:O26").Copy wb5.Range("O25").PasteSpecial xlPasteValues' wb2PQRY.Range("R25:R26").Copy wb5.Range("R25").PasteSpecial xlPasteValues' wb2PQRY.Range("U25:U26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("U25").PasteSpecial xlPasteValues' wb2PQRY.Range("X25:X26").Copy wb5.Range("X25").PasteSpecial xlPasteValues' wb2PQRY.Range("AA25:AA26").Copy wb5.Range("AA25").PasteSpecial xlPasteValues' wb2PQRY.Range("AD25:AD26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("AD25").PasteSpecial xlPasteValues' wb2PQRY.Range("AG25:AG26").Copy wb5.Range("AG25").PasteSpecial xlPasteValues' wb2PQRY.Range("AJ25:AJ26").Copy wb5.Range("AJ25").PasteSpecial xlPasteValues' wb2PQRY.Range("AM25:AM26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("AM25").PasteSpecial xlPasteValues' wb2PQRY.Range("AP25:AP26").Copy wb5.Range("AP25").PasteSpecial xlPasteValues' wb2PQRY.Range("AS25:AS26").Copy wb5.Range("AS25").PasteSpecial xlPasteValues' wb2PQRY.Range("AV25:AV26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("AV25").PasteSpecial xlPasteValues' wb2PQRY.Range("AY25:AY26").Copy wb5.Range("AY25").PasteSpecial xlPasteValues' wb2PQRY.Range("BB25:BB26").Copy wb5.Range("BB25").PasteSpecial xlPasteValues' wb2PQRY.Range("BE25:BE26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("BE25").PasteSpecial xlPasteValues' wb2PQRY.Range("BH25:BH26").Copy wb5.Range("BH25").PasteSpecial xlPasteValues' wb2PQRY.Range("BK25:BK26").Copy wb5.Range("BK25").PasteSpecial xlPasteValues' wb2PQRY.Range("BN25:BN26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("BN25").PasteSpecial xlPasteValues' wb2PQRY.Range("BQ25:BQ26").Copy wb5.Range("BQ25").PasteSpecial xlPasteValues' wb2PQRY.Range("BT25:BT26").Copy wb5.Range("BT25").PasteSpecial xlPasteValues' wb2PQRY.Range("BW25:BW26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("BW25").PasteSpecial xlPasteValues' wb2PQRY.Range("BZ25:BZ26").Copy wb5.Range("BZ25").PasteSpecial xlPasteValues' wb2PQRY.Range("CC25:CC26").Copy wb5.Range("CC25").PasteSpecial xlPasteValues' wb2PQRY.Range("CF25:CF26").Copy ' LEAVE ANNUAL/WITH OUT PAY wb5.Range("CF25").PasteSpecial xlPasteValues' wb2PQRY.Range("CI25:CI26").Copy wb5.Range("CI25").PasteSpecial xlPasteValues' wb2PQRY.Range("CL25:CL26").Copy wb5.Range("CL25").PasteSpecial xlPasteValues'' ### Copy BASE RATE HRS' wb2PQRY.Range("E23").Copy ' BASE RATE HRS wb5.Range("E23").PasteSpecial xlPasteValues' wb2PQRY.Range("H23").Copy wb5.Range("H23").PasteSpecial xlPasteValues' wb2PQRY.Range("K23").Copy wb5.Range("K23").PasteSpecial xlPasteValues' wb2PQRY.Range("N23").Copy ' BASE RATE HRS wb5.Range("N23").PasteSpecial xlPasteValues' wb2PQRY.Range("Q23").Copy wb5.Range("Q23").PasteSpecial xlPasteValues' wb2PQRY.Range("T23").Copy wb5.Range("T23").PasteSpecial xlPasteValues' wb2PQRY.Range("W23").Copy ' BASE RATE HRS wb5.Range("W23").PasteSpecial xlPasteValues' wb2PQRY.Range("Z23").Copy wb5.Range("Z23").PasteSpecial xlPasteValues' wb2PQRY.Range("AC23").Copy wb5.Range("AC23").PasteSpecial xlPasteValues' wb2PQRY.Range("AF23").Copy ' BASE RATE HRS wb5.Range("AF23").PasteSpecial xlPasteValues' wb2PQRY.Range("AI23").Copy wb5.Range("AI23").PasteSpecial xlPasteValues' wb2PQRY.Range("AL23").Copy wb5.Range("AL23").PasteSpecial xlPasteValues' wb2PQRY.Range("AO23").Copy ' BASE RATE HRS wb5.Range("AO23").PasteSpecial xlPasteValues' wb2PQRY.Range("AR23").Copy wb5.Range("AR23").PasteSpecial xlPasteValues' wb2PQRY.Range("AU23").Copy wb5.Range("AU23").PasteSpecial xlPasteValues' wb2PQRY.Range("AX23").Copy ' BASE RATE HRS wb5.Range("AX23").PasteSpecial xlPasteValues' wb2PQRY.Range("BA23").Copy wb5.Range("BA23").PasteSpecial xlPasteValues' wb2PQRY.Range("BD23").Copy wb5.Range("BD23").PasteSpecial xlPasteValues' wb2PQRY.Range("BG23").Copy ' BASE RATE HRS wb5.Range("BG23").PasteSpecial xlPasteValues' wb2PQRY.Range("BJ23").Copy wb5.Range("BJ23").PasteSpecial xlPasteValues' wb2PQRY.Range("BM23").Copy wb5.Range("BM23").PasteSpecial xlPasteValues' wb2PQRY.Range("BP23").Copy ' BASE RATE HRS wb5.Range("BP23").PasteSpecial xlPasteValues' wb2PQRY.Range("BS23").Copy wb5.Range("BS23").PasteSpecial xlPasteValues' wb2PQRY.Range("BV23").Copy wb5.Range("BV23").PasteSpecial xlPasteValues' wb2PQRY.Range("BY23").Copy ' BASE RATE HRS wb5.Range("BY23").PasteSpecial xlPasteValues' wb2PQRY.Range("CB23").Copy wb5.Range("CB23").PasteSpecial xlPasteValues' wb2PQRY.Range("CE23").Copy wb5.Range("CE23").PasteSpecial xlPasteValues' wb2PQRY.Range("CH23").Copy ' BASE RATE HRS wb5.Range("CH23").PasteSpecial xlPasteValues' wb2PQRY.Range("CK23").Copy wb5.Range("CK23").PasteSpecial xlPasteValues' wb2PQRY.Range("CN23").Copy wb5.Range("CN23").PasteSpecial xlPasteValues'' ### Copy Pre-Tax Deductions' wb2PQRY.Range("E28").Copy ' PRE-TAX DEDUCTION wb5.Range("E28").PasteSpecial xlPasteValues' wb2PQRY.Range("H28").Copy wb5.Range("H28").PasteSpecial xlPasteValues' wb2PQRY.Range("K28").Copy wb5.Range("K28").PasteSpecial xlPasteValues' wb2PQRY.Range("N28").Copy ' PRE-TAX DEDUCTION wb5.Range("N28").PasteSpecial xlPasteValues' wb2PQRY.Range("Q28").Copy wb5.Range("Q28").PasteSpecial xlPasteValues' wb2PQRY.Range("T28").Copy wb5.Range("T28").PasteSpecial xlPasteValues' wb2PQRY.Range("W28").Copy ' PRE-TAX DEDUCTION wb5.Range("W28").PasteSpecial xlPasteValues' wb2PQRY.Range("Z28").Copy wb5.Range("Z28").PasteSpecial xlPasteValues' wb2PQRY.Range("AC28").Copy wb5.Range("AC28").PasteSpecial xlPasteValues' wb2PQRY.Range("AF28").Copy ' PRE-TAX DEDUCTION wb5.Range("AF28").PasteSpecial xlPasteValues' wb2PQRY.Range("AI28").Copy wb5.Range("AI28").PasteSpecial xlPasteValues' wb2PQRY.Range("AL28").Copy wb5.Range("AL28").PasteSpecial xlPasteValues' wb2PQRY.Range("AO28").Copy ' PRE-TAX DEDUCTION wb5.Range("AO28").PasteSpecial xlPasteValues' wb2PQRY.Range("AR28").Copy wb5.Range("AR28").PasteSpecial xlPasteValues' wb2PQRY.Range("AU28").Copy wb5.Range("AU28").PasteSpecial xlPasteValues' wb2PQRY.Range("AX28").Copy ' PRE-TAX DEDUCTION wb5.Range("AX28").PasteSpecial xlPasteValues' wb2PQRY.Range("BA28").Copy wb5.Range("BA28").PasteSpecial xlPasteValues' wb2PQRY.Range("BD28").Copy wb5.Range("BD28").PasteSpecial xlPasteValues' wb2PQRY.Range("BG28").Copy ' PRE-TAX DEDUCTION wb5.Range("BG28").PasteSpecial xlPasteValues' wb2PQRY.Range("BJ28").Copy wb5.Range("BJ28").PasteSpecial xlPasteValues' wb2PQRY.Range("BM28").Copy wb5.Range("BM28").PasteSpecial xlPasteValues' wb2PQRY.Range("BP28").Copy ' PRE-TAX DEDUCTION wb5.Range("BP28").PasteSpecial xlPasteValues' wb2PQRY.Range("BS28").Copy wb5.Range("BS28").PasteSpecial xlPasteValues' wb2PQRY.Range("BV28").Copy wb5.Range("BV28").PasteSpecial xlPasteValues' wb2PQRY.Range("BY28").Copy ' PRE-TAX DEDUCTION wb5.Range("BY28").PasteSpecial xlPasteValues' wb2PQRY.Range("CB28").Copy wb5.Range("CB28").PasteSpecial xlPasteValues' wb2PQRY.Range("CE28").Copy wb5.Range("CE28").PasteSpecial xlPasteValues' wb2PQRY.Range("CH28").Copy ' PRE-TAX DEDUCTION wb5.Range("CH28").PasteSpecial xlPasteValues' wb2PQRY.Range("CK28").Copy wb5.Range("CK28").PasteSpecial xlPasteValues' wb2PQRY.Range("CN28").Copy wb5.Range("CN28").PasteSpecial xlPasteValues'' ### Copy Post-Tax Deductions' wb2PQRY.Range("E31").Copy ' POST-TAX DEDUCTION wb5.Range("E31").PasteSpecial xlPasteValues' wb2PQRY.Range("H31").Copy wb5.Range("H31").PasteSpecial xlPasteValues' wb2PQRY.Range("K31").Copy wb5.Range("K31").PasteSpecial xlPasteValues' wb2PQRY.Range("N31").Copy ' POST-TAX DEDUCTION wb5.Range("N31").PasteSpecial xlPasteValues' wb2PQRY.Range("Q31").Copy wb5.Range("Q31").PasteSpecial xlPasteValues' wb2PQRY.Range("T31").Copy wb5.Range("T31").PasteSpecial xlPasteValues' wb2PQRY.Range("W31").Copy ' POST-TAX DEDUCTION wb5.Range("W31").PasteSpecial xlPasteValues' wb2PQRY.Range("Z31").Copy wb5.Range("Z31").PasteSpecial xlPasteValues' wb2PQRY.Range("AC31").Copy wb5.Range("AC31").PasteSpecial xlPasteValues' wb2PQRY.Range("AF31").Copy ' POST-TAX DEDUCTION wb5.Range("AF31").PasteSpecial xlPasteValues' wb2PQRY.Range("AI31").Copy wb5.Range("AI31").PasteSpecial xlPasteValues' wb2PQRY.Range("AL31").Copy wb5.Range("AL31").PasteSpecial xlPasteValues' wb2PQRY.Range("AO31").Copy ' POST-TAX DEDUCTION wb5.Range("AO31").PasteSpecial xlPasteValues' wb2PQRY.Range("AR31").Copy wb5.Range("AR31").PasteSpecial xlPasteValues' wb2PQRY.Range("AU31").Copy wb5.Range("AU31").PasteSpecial xlPasteValues' wb2PQRY.Range("AX31").Copy ' POST-TAX DEDUCTION wb5.Range("AX31").PasteSpecial xlPasteValues' wb2PQRY.Range("BA31").Copy wb5.Range("BA31").PasteSpecial xlPasteValues' wb2PQRY.Range("BD31").Copy wb5.Range("BD31").PasteSpecial xlPasteValues' wb2PQRY.Range("BG31").Copy ' POST-TAX DEDUCTION wb5.Range("BG31").PasteSpecial xlPasteValues' wb2PQRY.Range("BJ31").Copy wb5.Range("BJ31").PasteSpecial xlPasteValues' wb2PQRY.Range("BM31").Copy wb5.Range("BM31").PasteSpecial xlPasteValues' wb2PQRY.Range("BP31").Copy ' POST-TAX DEDUCTION wb5.Range("BP31").PasteSpecial xlPasteValues' wb2PQRY.Range("BS31").Copy wb5.Range("BS31").PasteSpecial xlPasteValues' wb2PQRY.Range("BV31").Copy wb5.Range("BV31").PasteSpecial xlPasteValues' wb2PQRY.Range("BY31").Copy ' POST-TAX DEDUCTION wb5.Range("BY31").PasteSpecial xlPasteValues' wb2PQRY.Range("CB31").Copy wb5.Range("CB31").PasteSpecial xlPasteValues' wb2PQRY.Range("CE31").Copy wb5.Range("CE31").PasteSpecial xlPasteValues' wb2PQRY.Range("CH31").Copy ' POST-TAX DEDUCTION wb5.Range("CH31").PasteSpecial xlPasteValues' wb2PQRY.Range("CK31").Copy wb5.Range("CK31").PasteSpecial xlPasteValues' wb2PQRY.Range("CN31").Copy wb5.Range("CN31").PasteSpecial xlPasteValues Else: PQRY_YesNo = "NO" End If'' ###################################################################' ### BUDGET WORKOUT' Dim BDGT_YesNo Dim mboxBDGT As Integer Dim strPromptBDGT As String strPromptBDGT = "Do you want to copy - BUDGET DATA??" mboxBDGT = MsgBox(strPromptBDGT, vbYesNo, strTitle) ' Check pressed button If mboxBDGT = vbYes Then' wb2BDGT.Range("A6:D26").Copy ' DESCRIPTIONS wb4.Range("A6").PasteSpecial xlPasteValues' wb2BDGT.Range("F6:AI29").Copy ' AMOUNTS WITHDRAWLS wb4.Range("F6").PasteSpecial xlPasteComments: wb4.Range("F6").PasteSpecial xlPasteValues' wb2BDGT.Range("A27:C28").Copy ' SAVINGS DETAILS wb4.Range("A27").PasteSpecial xlPasteComments: wb4.Range("A27").PasteSpecial xlPasteValues' wb2BDGT.Range("F34:AI34").Copy ' ADD EXTRA MONEY wb4.Range("F34").PasteSpecial xlPasteComments: wb4.Range("F34").PasteSpecial xlPasteValues' wb2BDGT.Range("E36").Copy ' START BOX wb4.Range("E36").PasteSpecial xlPasteValues Else: BDGT_YesNo = "NO" End If'' ###################################################################' ### Time to close the OLD Database wb2.Close False' Application.CalculateFullRebuild ' REFRESH DATA'Application.ScreenUpdating = True ' ALLOW SCREEN FLICKER AGAINApplication.EnableEvents = True ' Need to ENABLE Other Macros Again' ### Let People Know ALL COMPLETE Dim updtCmpl As Integer Dim strPromptEND As String Dim strTitleEND As String ' Prompt strPromptEND = "YOUR DATA HAS NOW BEEN TRANSFERRED ACROSS.. !!!" _ & vbCr & " " _ & vbCr & " YOU MUST DO THE FOLLOWING" _ & vbCr & " " _ & vbCr & "CHECK - Pay Rates are Correct" _ & vbCr & "CLICK REFRESH ALL" _ & vbCr & "SAVE - Be sure to save before any more changes.." _ & vbCr & " " ' Dialog's Title strTitleEND = " !!!" ' Display MessageBox updtCmpl = MsgBox(strPromptEND, vbInformation, strTitleEND)'End Sub
  3. Apologies if this is a big Code... ' _startup.vbs ' %%% This StartUp Program is Automatically initiated during Windows Startup ' ' @@@@ TO CANCEL THE START-UP PROCESS Alt + F4 Then close Script Error @@@@ ' ' ************************ ' ** THIS SCRIPT WILL RUN AUTOMATICALLY DURING BOOT UP STAGE.. ' ** IT WILL THEN LOG THE TIME THAT COMPUTER WAS RESTARTED & ALL PROGRAMS LOADED.. ' ** ONCE LOADED THE LOG FILE WILL BE UPDATED AND EMAILED TO ADMINISTRATOR. ' ************************ ' ** HERE WE GO !!! ** ' ************************ Dim strDateLayout, strCurrentTime, strCntDwnTimer1, strCntDwnTimer2, strCntDwnHTML1, strCntDwnHTML2, strCntDwnHTML3 Dim strStatus1, strStatus2, strStatus3, strStatus4, strLOGDirectory, strLOGFile Dim objWshell, oIE, oIEDoc Set FSO = CreateObject("Scripting.FileSystemObject") Set objWshell = Wscript.CreateObject("Wscript.Shell") ' ' SETTINGS THAT CAN BE CHANGED ' strDateLayout = WeekDayName(WeekDay(Now()),1) & " " _ & Right("0" & DatePart("d", Now),2) & "-" _ & MonthName(Month(Now()),1) & "-" _ & DatePart("yyyy", Now) & " -- " _ & FormatDateTime(Now(),vbShortTime) & " (" & Right("0" & DatePart("s", Now),2) & " secs)" ' Sat 03-Nov-2012 -- 03:19 (36 secs) strCurrentTime = "<b>Time Process Started: </b>" & strDateLayout strCntDwnTimer1 = "<font size='6'>Approx <font size='7' color='red'><b>" strCntDwnTimer2 = "</b></font> Seconds Remaining</font>" strCntDwnHTML1 = "<br> <br> <br> <table align='center'><tr><td align='center'><font size='7'><b>PLEASE WAIT !!!</b></font></td></tr><tr><td> <br><br> </td></tr><tr><td align='center'>" strCntDwnHTML2 = "</td></tr><tr><td align='center'> <br> </td></tr><tr><td align='center'><font size='5'><b><u>WE ARE CURRENTLY</u></b></font><br><br><b>" ' strCntDwnHTML3 = "</b></td></tr><tr><td align='center'> <br> <br> <br> </td></tr><tr><td align='center'><img height='116' width='505' src='\\localhost\images\LOADING.gif'></td></tr></table>" strStatus1 = "<font size='5' color='green'>APPENDING THE COMPUTER RESTART TIME TO SYSTEM LOG</font>" strStatus2 = "<font size='5' color='green'>WAITING FOR PRINTER TO INITIATE</font>" strStatus3 = "<font size='5' color='green'>SENDING EMAIL TO <font color='blue'>'Administrator'<font color='green'> ADVISING SYSTEM RESTARTED</font>" strStatus4 = "<font size='5' color='green'>REBOOTING <font color='blue'>000 Nav Turnout System</font>" strLOGDirectory = "C:\_DRFB_StationPC\Logs\" strLOGFile = "Shutdown_Restart_LOG.txt" ' ' ##### DO NOT TOUCH ANYTHING BELOW THIS LINE ' ' ************************************************ ' ** CHECK CURRENT DATE TO SEE IF BACK-UP OF ** ' ** SYSTEM LOG IS REQUIRED. IF SO THEN DO IT ** ' ************************************************ On Error Resume Next Dim strDayNumb, strBkupDate, strFileFolder, strBkupFolder, strFileCLEAN strDayNumb = Right("0" & DatePart("d", Now),2) strBkupDate = DatePart("yyyy", Now) & "-" & Right("0" & DatePart("m", Now),2) & "-" & Right("0" & DatePart("d", Now),2) & "_" & Right("0" & Hour(Now),2) & Right("0" & Minute(Now),2) & "." & Right("0" & Second(Now),2) & "sec_"' 2012-11-20_LOGFile.txt strFileFolder = strLOGDirectory & strLOGFile strBkupFolder = strLOGDirectory & "_BackUps\" strFileCLEAN = strBkupFolder & "CLEAN\" & strLOGFile Dim FSO Set FSO = CreateObject("Scripting.FileSystemObject") If strDayNumb = 01 Then ' Checking for 1st Day of Month 'MsgBox "Today's date is " & strDayNumb & vbCrLf & "Which means we need to BackUp Log File" ' If FSO.FileExists(strFileFolder) Then FSO.CopyFile strFileFolder ,strBkupFolder wscript.sleep(5000) ' ** Pause for a few seconds (5 sec) FSO.MoveFile strBkupFolder & strLOGFile ,strBkupFolder & strBkupDate & strLOGFile ' Rename File with Date Appended wscript.sleep(5000) ' ** Pause for a few seconds (5 sec) ErrLOGFile1 = 0 Else ErrLOGFile1 = 1 End If If FSO.FileExists(strFileFolder) Then FSO.DeleteFile strFileFolder ErrLOGFile2 = 0 Else ErrLOGFile2 = 1 End If wscript.sleep(5000) ' ** Pause for a few seconds (5 sec) FSO.CopyFile strFileCLEAN ,strLOGDirectory Else 'MsgBox "Today is not the day to BackUp Logs" End If 'MsgBox "PROCESS COMPLETE" ' ' ERROR DATA ' If Err.Number <> 0 Or ErrLOGFile = 1 Or ErrLOGFile2 = 1 Then ' ****************************** ' ** ERROR DETAILS IN LOG ** ' ****************************** Dim strErrLOGText strErrLOGText = " ********** " & vbCrLf & "ERR LOGFILE: " & strDateLayout & " == Error Trying to BackUp System Log File - Possible File/Folder Not Exist == " & vbCrLf & "ErrorLOGFile 1 status is " & ErrLOGFile1 & " -- ErrorLOGFile 2 status is " & ErrLOGFile2 & vbCrLf & " *** ERROR IGNORED -- Will Continue To Load *** " & vbCrLf & " ********** " 'Err LogFile: Sat 03-Nov-2012 -- 03:19 (36 secs) ' Now Append The Restarted Time Const ErrLOGForAppending = 8 ' ForAppending = 8 ForReading = 1 ForWriting = 2 Set objTextFile = FSO.OpenTextFile _ (strLOGDirectory & strLOGFile, ErrLOGForAppending, True) objTextFile.WriteLine(strErrLOGText) ' Writes strText every time script Runs objTextFile.Close End If wscript.sleep(5000) ' ** Pause for a few seconds (5 sec) ' ************************ ' ** END OF BACK-UP ** ' ************************ ' ' Initialize the "Please Wait" window ' Set oIE = Wscript.CreateObject("InternetExplorer.Application") oIE.Navigate "about:blank" do while oIE.busy : wscript.sleep 10 : loop Set oIEDoc = oIE.Document ' As it's an Internet Explorer window, we must get rid of the toolbars ' oIE.AddressBar = False oIE.StatusBar = False oIE.ToolBar = False oIE.FullScreen = True oIE.Document.Body.Scroll = "no" oIE.document.title = "-- LOADING SETTINGS -- Please StandBy........" 'oIE.height=700 'oIE.width=550 oIE.Resizable = False oIE.Visible = True ' Display HTML within this window with animated Loading Image ' '## Message 1 '######################### Dim CountDown : CountDown = 45 ' Total Amount of CountDown Time Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus1 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 40 ' Ready for Next Step ' ****************************** ' ** RUN RESTART TIME LOG ** ' ****************************** Dim strText strText = " Restarted: " & strDateLayout 'Restarted: Sat 03-Nov-2012 -- 03:19 (36 secs) ' Now Append The Restarted Time Const ForAppending = 8 ' ForAppending = 8 ForReading = 1 ForWriting = 2 Set objTextFile = FSO.OpenTextFile _ (strLOGDirectory & strLOGFile, ForAppending, True) objTextFile.WriteLine(strText) ' Writes strText every time script Runs objTextFile.Close Set sMsg = Nothing '## Message 2 '######################### Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus2 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 30 ' Ready for Next Step ' ***************************************** ' ** WAITING FOR PRINTER TO INITIATE ** ' ***************************************** Set sMsg = Nothing '## Message 3 '######################### Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus3 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 20 ' Ready for Next Step ' ' ERROR DATA ' On Error Resume Next ' ' ************************************************** ' ** LET's SEND EMAIL - To Administrators !!! ** ' ************************************************** Dim oName, ODomain, oMyIP, oTo, oSender, oSubject, oTextBody, oAddAttach, oAttachment1, oAttachment2, oAttachment3, oAttachment4, oMsgBox ' Get the computer name Set WshNetwork = CreateObject("WScript.Network") oName = WshNetwork.ComputerName ' ***************************** ' ** Set Information Below ** ' ***************************** ' ' Email Domain Name ODomain = "mydomain.org.au" ' SMTP - Outgoing Email Server oMyIP = "mail.ourserver.com.au" ' Recipient Email Address {Separate using ; if Multiple Recipients} oTo = "us@mydomain.org.au" ' Sender Name oSender = "Main_PC" ' NO SPACES ALLOWED ' Email Subject oSubject = "Main PC StartUp/Reboot !!" ' Email Text Body {You can have 2 different Views for Emails Sent. Plain Text / HTML which can display as both the same or both completely different.} oTextBody = "MAIN PC -- StartUp / Reboot" & vbCrLf & vbCrLf & "The Main PC has Rebooted and successfully Loaded"& vbCrLf &"At: " & now & vbCrLf & vbCrLf &"Computer Name: " & oName & vbCrLf & vbCrLf &"You should also receive an E-Mail from { Server } advising of a possible Reason For Reboot (if occurred)."& vbCrLf & vbCrLf &"Attached are System Log Files [Showing STARTUP / RESTART Times]"& vbCrLf & vbCrLf &"Regards,"& vbCrLf &"MAIN PC"& vbCrLf & vbCrLf &" " oHTMLBody = "<html><body><h1>MAIN PC -- StartUp / Reboot</h1><br>The Main PC has Rebooted and successfully Loaded<br>At: "& now &"<br><br><b>Computer Name:</b> "& oName &"<br><br>You should also receive an E-Mail from { Server } advising of a possible Reason For Reboot (if occurred).<br><br>Attached are System Log Files [Showing STARTUP / RESTART Times]<br><br>Regards,<br>MAIN PC<br> <br> </body></html>" ' ' Email Attachment/s ' 0 = No Email Attachment ' 1 = Includes Email Attachment oAddAttach1 = 1 ' ## Attachment 1 oAttachment1= strLOGDirectory & strLOGFile ' oAddAttach2 = 0 ' ## Attachment 2 oAttachment2 = "C:\Document (2).txt" ' ' Message Box oMsgBox = 0 ' 0 = No Sent MsgBox ' 1 = Display Sent MsgBox ' ' *************************************** ' ** DO NOT CHANGE ANYTHING BELOW HERE ** ' *************************************** ' ' Setting the VB constants as they do not exist within VBScript Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _ cdoSendUsingPort = 2, _ cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver" ' Create the CDO connections Dim iMsg, iConf, Flds Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") Set Flds = iConf.Fields ' SMTP server configuration With Flds .Item(cdoSendUsingMethod) = cdoSendUsingPort ' Set the SMTP server address here .Item(cdoSMTPServer) = oMyIP .Update End With ' Set the Message properties With iMsg Set .Configuration = iConf .To = oTo '.CC = "user@domain.com" ' Uncomment this Line (.CC) to enable Carbon Copy '.BCC = "user@domain.com" ' Uncomment this Line (.BCC) to enable Blind Carbon Copy .From = oSender & "@" & oDomain .Subject = oSubject .TextBody = oTextBody .HTMLBody = oHTMLBody End With ' An attachment/s can be included If oAddAttach1 = 1 then iMsg.AddAttachment oAttachment1 End If If oAddAttach2 = 1 then iMsg.AddAttachment oAttachment2 End If ' Send the message iMsg.Send Set iMsg = Nothing ' Confirmation Message Box If oMsgBox = 1 then MsgBox "SYSTEM EMAIL SENT" Else End If ' ' ERROR DATA ' If Err.Number <> 0 Then Set sMsg = Nothing ' ****************************** ' ** ERROR DETAILS IN LOG ** ' ****************************** Dim strErrText strErrText = " ********** " & vbCrLf & " ERROR INET: " & strDateLayout & " == Error while trying to send ADMIN Email" & vbCrLf & " *** ERROR -- Possible Internet Not Working " & vbCrLf & " *** ERROR IGNORED -- Will Continue To Load 'Programs' *** " & vbCrLf & " ********** " 'Restarted: Sat 03-Nov-2012 -- 03:19 (36 secs) ' Now Append The Restarted Time Const ErrForAppending = 8 ' ForAppending = 8 ForReading = 1 ForWriting = 2 Set objTextFile = FSO.OpenTextFile _ (strLOGDirectory & strLOGFile, ErrForAppending, True) objTextFile.WriteLine(strErrText) ' Writes strText every time script Runs objTextFile.Close End If ' '## Message 4 '######################### Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus4 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 0 ' Ready for Next Step ' ********************** ' ** LOAD PROGRAM ** ' ********************** objWshell.run """C:\Folder\File.exe""" Set sMsg = Nothing ' Time To Close Down The Script and "Please Wait" window ' Set oIEDoc = Nothing oIE.Quit Set oIE = Nothing Set objFSO = Nothing Set objWshell = Nothing Set WshShell = Nothing WScript.Quit
  4. Cheers for the info. Muchly Appreciated.. I have been playing around with your Script.. How do i get it to open programs etc when the counter gets to a certain # (seconds)? Also how to get it to display text at a certain #. I've tried to get it to display using the below by no luck. If i gave you a copy of my coding (.vbs) do you think you could assist in changing it into .hta?
  5. I notice at the end of your hta it has the cancel buttons etc. i don't understand how i can get programs to load in the background like i have in the .vbs? i'm quite confident so far in working with .vbs but all of my knowledge is self taught so it is hard to change the skills into a new coding language.. I know a little bit about php but that's about it.. It took me a number of attempts to get the .vbs i have above let alone learning hta.. Any assistance would be greatly appreciated. What are the main differences between hta & vbs? I have seen numerous references to hta in the past but never looked into it all that much.. Cheers Edit: if i can keep the vbs that would be great but just need to know how to get it to cancel the whole script by clicking a button on the splash screen (IE window that is maximised)..
  6. Hi All, This has bugged me fore some time as i have the (On Error Resume Next) which means everytime i try to cancel the Script it still Loads the next step.. I have a .vbs that run automatically when windows boots.. It loads a couple of programs while displaying a LOADING Splash Screen (which open within IE). What i need is a way to Ws Quit the Script and close the IE Loading Screen. But if nothing clicks on the Quit (hyperlink or something) then it just loads as per normal.. EXAMPLE ' ' Initialize the "Please Wait" window ' Set oIE = Wscript.CreateObject("InternetExplorer.Application") oIE.Navigate "about:blank" do while oIE.busy : wscript.sleep 10 : loop Set oIEDoc = oIE.Document ' As it's an Internet Explorer window, we must get rid of the toolbars ' oIE.AddressBar = False oIE.StatusBar = False oIE.ToolBar = False oIE.FullScreen = True oIE.Document.Body.Scroll = "no" oIE.document.title = "-- LOADING SETTINGS -- Please StandBy........" 'oIE.height=700 'oIE.width=550 oIE.Resizable = False oIE.Visible = True ' Display HTML within this window with animated Loading Image ' '## Message 1 '######################### Dim CountDown : CountDown = 45 ' Total Amount of CountDown Time Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus1 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 40 ' Ready for Next Step ' ****************************** ' ** RUN RESTART TIME LOG ** ' ****************************** Dim strText strText = " Restarted: " & strDateLayout 'Eg. Restarted: Sat 03-Nov-2012 -- 03:19 (36 secs) ' Now Append The Restarted Time Const ForAppending = 8 ' ForAppending = 8 ForReading = 1 ForWriting = 2 Set objTextFile = FSO.OpenTextFile _ (strLOGDirectory & strLOGFile, ForAppending, True) objTextFile.WriteLine(strText) ' Writes strText every time script Runs objTextFile.Close Set sMsg = Nothing '## Message 2 '######################### Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus2 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 30 ' Ready for Next Step ' ***************************************** ' ** WAITING FOR PRINTER TO INITIATE ** ' ***************************************** Set sMsg = Nothing '## Message 3 '######################### Do wscript.sleep (1000) ' Pause for a second CountDown = CountDown - 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sMsg= strCurrentTime & strCntDwnHTML1 & strCntDwnTimer1 & CountDown & strCntDwnTimer2 & strCntDwnHTML2 & strStatus3 & strCntDwnHTML3 oIEDoc.Body.Innerhtml= sMsg '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Loop until CountDown = 0 ' Ready for Next Step And then it does other stuff.. But what i mainly need is for a Link to show up in the HTML of the Loading Splash Screen to allow the Admin to cancel the Script if Interaction is Required.. Please note i use .vbs not .vb Thanks..