Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account



Photo

Excel VBA (Procedure to Large)

- - - - -

  • Please log in to reply
3 replies to this topic

#1
drfb

drfb
  • Member
  • 6 posts
  • Joined 27-January 13
  • OS:Windows 7 x86
  • Country: Country Flag

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 Worksheet
Dim wb2 As Workbook
Dim wb3 As Worksheet
Dim wb4 As Worksheet
Dim 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 Errors
Application.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 AGAIN
Application.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



How to remove advertisement from MSFN

#2
gunsmokingman

gunsmokingman

    MSFN Master

  • Super Moderator
  • 2,447 posts
  • Joined 02-August 03
  • OS:none specified
  • Country: Country Flag

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



GunSmokingMan



#3
drfb

drfb
  • Member
  • 6 posts
  • Joined 27-January 13
  • OS:Windows 7 x86
  • Country: Country Flag
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..



#4
gunsmokingman

gunsmokingman

    MSFN Master

  • Super Moderator
  • 2,447 posts
  • Joined 02-August 03
  • OS:none specified
  • Country: Country Flag
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




GunSmokingMan






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users