drfb Posted July 21, 2013 Share Posted July 21, 2013 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 Officeare 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 CODESub 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 More sharing options...
gunsmokingman Posted July 21, 2013 Share Posted July 21, 2013 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 IfNow I am not sure if this will work in a Excel but here is a less coded MsgboxDim 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 More sharing options...
drfb Posted July 21, 2013 Author Share Posted July 21, 2013 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 IfThanks 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 nowuse ' Call Update_HW(wb2HW, wb1) ' where Update_HW is the Sub & wb2HW, wb1 are theother 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 endhas 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 More sharing options...
gunsmokingman Posted July 21, 2013 Share Posted July 21, 2013 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 thatresult 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 IfHere is what using Array and a For Each Loop does for your code, all youwould have to is change all the WScript.Echo _Example Of Changes for your scriptChange 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 putwb2PR.Range("E1:F1").Copywb3.Range("E1").PasteSpecialwb2PR.Range("B3:E3").Copywb3.Range("B3").PasteSpecialwb2PR.Range("E5:F5").Copywb3.Range("E5").PasteSpecialwb2PR.Range("B7:E7").Copywb3.Range("B7").PasteSpecialwb2PR.Range("E9:F9").Copywb3.Range("E9").PasteSpecialwb2PR.Range("B11:E11").Copywb3.Range("B11").PasteSpecialwb2PR.Range("E13:F13").Copywb3.Range("E13").PasteSpecialwb2PR.Range("B15:E15").Copywb3.Range("B15").PasteSpecialwb2PR.Range("E17:F17").Copywb3.Range("E17").PasteSpecialwb2PR.Range("B19:E19").Copywb3.Range("B19").PasteSpecialwb2PR.Range("E21:F21").Copywb3.Range("E21").PasteSpecialwb2PR.Range("B23:E23").Copywb3.Range("B23").PasteSpecialwb2PR.Range("C26:C27").Copywb3.Range("C26").PasteSpecialwb2PR.Range("E26").Copywb3.Range("E26").PasteSpecialwb2PR.Range("C30:C31").Copywb3.Range("C30").PasteSpecialwb2PR.Range("E30").Copywb3.Range("E30").PasteSpecialwb2PR.Range("O10").Copywb3.Range("O10").PasteSpecial xlPasteValues Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now