Jump to content

Excel VBA (Procedure to Large)


Recommended Posts

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
Link to comment
Share on other sites


Since I do not use Excel are you looking to clean up thing like Messageboxes and lessen the code.

Example Your Msgbox

    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

Now I am not sure if this will work in a Excel but here is a less coded Msgbox

Dim PR_YesNo  If MsgBox( _  "Would You Like To Copy - Pay Rates",4132,"Payrates Copy") = 6 Then   PR_YesNo = "YES"  Else    PR_YesNo = "NO"  End If
Link to comment
Share on other sites

Dim PR_YesNo  If MsgBox( _  "Would You Like To Copy - Pay Rates",4132,"Payrates Copy") = 6 Then   PR_YesNo = "YES"  Else    PR_YesNo = "NO"  End If

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..

Link to comment
Share on other sites

I will look threw all the code and try to figure out some Arrays and some For If to lessen the code.

Here is another code saving for you on the Msgbox, you are using a Variable to check for Yes Or No.

Coded this way you will not need the variable because you will be using the Msgbox and using that

result for your script.

'-> Some Text Details About Purpose Of Messagebox  If MsgBox( _  "Would You Like To Copy - Pay Rates",4132,"Payrates Copy") = 6 Then'-> Code Here For The Yes Reply  Else'-> Code Here For The No Reply  End If
Here is what using Array and a For Each Loop does for your code, all you

would have to is change all the WScript.Echo _

Example Of Changes for your script

Change From

      WScript.Echo _      "wb2PR.Range(" & Chr(34) & i & Chr(34) & ").Copy" & vbCrLf & _      "wb3.Range(" & Chr(34) & j(0) & Chr(34) & ").PasteSpecial"
Change To

      wb2PR.Range(Chr(34) & i & Chr(34)).Copy      wb3.Range(Chr(34) & j(0) & Chr(34)).PasteSpecial
'-> Pay Rate   If MsgBox( _  "Would You Like To Copy - Pay Rates",4132,"Payrates Copy") = 6 Then     Dim PyRts :PyRts = Array( _    "E1:F1", _    "B3:E3", _    "E5:F5", _    "B7:E7", _    "E9:F9", _    "B11:E11", _    "E13:F13", _    "B15:E15", _    "E17:F17", _    "B19:E19", _    "E21:F21", _    "B23:E23", _    "C26:C27", _    "C30:C31")    Dim i, j '-> Loop Threw The Array     For Each i In PyRts'-> Split i And Use First Value      j = Split(i,":")       WScript.Echo _      "wb2PR.Range(" & Chr(34) & i & Chr(34) & ").Copy" & vbCrLf & _      "wb3.Range(" & Chr(34) & j(0) & Chr(34) & ").PasteSpecial"      If UCase(j(0)) = "C26" Then'-> EXTRA TAX #1 Date       WScript.Echo _      "wb2PR.Range(" & Chr(34) & "E26" & Chr(34)& ").Copy" & vbCrLf & _      "wb3.Range(" & Chr(34) & "E26" & Chr(34)& ").PasteSpecial"      End If       If UCase(j(0)) = "C30" Then'-> EXTRA TAX #2 Date       WScript.Echo _      "wb2PR.Range(" & Chr(34) & "E30" & Chr(34)& ").Copy" & vbCrLf & _      "wb3.Range(" & Chr(34) & "E30" & Chr(34)& ").PasteSpecial"      End If          Next'-> Primary ESO       WScript.Echo _   "wb2PR.Range(" & Chr(34) & "O10" & Chr(34)& ").Copy" & vbCrLf & _   "wb3.Range(" & Chr(34) & "O10" & Chr(34)& ").PasteSpecial xlPasteValues"        End If 
Produces this out put

wb2PR.Range("E1:F1").Copy

wb3.Range("E1").PasteSpecial

wb2PR.Range("B3:E3").Copy

wb3.Range("B3").PasteSpecial

wb2PR.Range("E5:F5").Copy

wb3.Range("E5").PasteSpecial

wb2PR.Range("B7:E7").Copy

wb3.Range("B7").PasteSpecial

wb2PR.Range("E9:F9").Copy

wb3.Range("E9").PasteSpecial

wb2PR.Range("B11:E11").Copy

wb3.Range("B11").PasteSpecial

wb2PR.Range("E13:F13").Copy

wb3.Range("E13").PasteSpecial

wb2PR.Range("B15:E15").Copy

wb3.Range("B15").PasteSpecial

wb2PR.Range("E17:F17").Copy

wb3.Range("E17").PasteSpecial

wb2PR.Range("B19:E19").Copy

wb3.Range("B19").PasteSpecial

wb2PR.Range("E21:F21").Copy

wb3.Range("E21").PasteSpecial

wb2PR.Range("B23:E23").Copy

wb3.Range("B23").PasteSpecial

wb2PR.Range("C26:C27").Copy

wb3.Range("C26").PasteSpecial

wb2PR.Range("E26").Copy

wb3.Range("E26").PasteSpecial

wb2PR.Range("C30:C31").Copy

wb3.Range("C30").PasteSpecial

wb2PR.Range("E30").Copy

wb3.Range("E30").PasteSpecial

wb2PR.Range("O10").Copy

wb3.Range("O10").PasteSpecial xlPasteValues

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...