Replace code not executing correctly - I'm confused as to why please help!

rkol297

rkol297

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a code that uses replacements to rename items in a few columns which are used later to make pivot tables. What I'm experiencing is that the code seems to be executing multiple times on the same cell.
Code: 
8/21/2012 14:50	8/22/2012	SPO	RMIP	PAH INJ	PAH PAH TRC  0.18333348/21/2012 15:07	8/22/2012	SPO	RMIP	PAH INJ	PAH PAH TRC  0.1833334
As you can see in the results above in column F instead of the code just executing and renaming the field PAH TRC it is incorrectly naming it PAH PAH TRC.Here is the code below if any one has a better way of renaming these fields or know why this code is executing correctly for most but incorrectly for the Column C value of RMIP please let me know as I would greatly appreciate it.
Code: 
Sub RSLDAILYHOURS()'' RSLDAILYHOURS MacroWith Application        .ScreenUpdating = False        .Calculation = xlCalculationManual        .DisplayAlerts = False        .EnableEvents = FalseEnd WithDim ws As Object    Dim LCell As Range    Application.Calculation = xlCalculationManual    For Each ws In ActiveWorkbook.Sheets    On Error Resume Next    ws.Activate    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)    LCell.Formula = UCase(LCell.Formula)    NextNext ws    ActiveSheet.Select    ActiveSheet.Name = "RPH DAILY PRODUCTIVITY"    Columns("D:D").Select    Selection.Copy    Columns("E:E").Select    ActiveSheet.Paste    Columns("F:F").Select    ActiveSheet.Paste    Columns("G:G").Select    ActiveSheet.Paste    Application.CutCopyMode = False    Range("E1").Select    ActiveCell.FormulaR1C1 = "GROUPER"    Range("F1").Select    ActiveCell.FormulaR1C1 = "TRC"    Range("G1").Select    ActiveCell.FormulaR1C1 = "LOCKING TIME"    Range("A1").SelectColumns("E:E").SelectSelection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYME", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVS", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENC", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENT", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCR", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LETA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="SILD", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TPAP", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAB", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVC", Replacement:="REVA SUSP", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="F100", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAC", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPOP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FL50", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMO", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIV", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ALDZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ELAP", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VPRV", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LUMZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="MYOZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="BERT", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FZYR", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ARAL", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGF", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="HIZA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLBO", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GLSA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZEMA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ACTP", Replacement:="ACTI", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("F:F").SelectSelection.Replace What:="ACTP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="PAH TRAC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("G:G").SelectSelection.Replace What:="ACTP", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Columns("A:H").Select    Selection.Columns.AutoFit    Range("A1").Select    Sheets("RPH DAILY PRODUCTIVITY").Select    Sheets("RPH DAILY PRODUCTIVITY").Copy After:=Sheets(1)    Sheets("RPH DAILY PRODUCTIVITY (2)").Select    Sheets("RPH DAILY PRODUCTIVITY (2)").Name = "NETWORK DOUBLE SHIPS"    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select  Dim CelDS As Range, RngDS As Range, iDS As Long  Set RngDS = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS = RngDS.Count To 1 Step -1     If RngDS(iDS).Value = "PAH TRC" _            Then RngDS(iDS).EntireRow.Delete  Next iDS  Dim CelDS2 As Range, RngDS2 As Range, iDS2 As Long  Set RngDS2 = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS2 = RngDS2.Count To 1 Step -1     If RngDS2(iDS2).Value = "IG TRC" _            Then RngDS2(iDS2).EntireRow.Delete  Next iDS2    Sheets("RPH DAILY PRODUCTIVITY").Select    Dim CelREMOVENONPAHIG As Range, RngREMOVENONPAHIG As Range, iREMOVENONPAHIG As Long    Set RngREMOVENONPAHIG = Columns("D").SpecialCells(xlConstants, xlTextValues)    For iREMOVENONPAHIG = RngREMOVENONPAHIG.Count To 2 Step -1        Select Case Range("D" & iREMOVENONPAHIG).Value            Case "ACTP", "ADCC", "ADCR", "ALDZ", "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "ELAP", "FL50", "F100", "FABF", "FABZ", "FLBO", "FPAP", "FZYR", "GAMC", "GAMS", "GGIV", "GGSC", "GKIV", "GKSC", "GLSA", "GMPX", "HIZA", "IVGB", "IVGF", "IVGP", "IVGS", "LETA", "LUMZ", "MYOZ", "NGLF", "NGLZ", "REMO", "REMP", "REVA", "REVC", "RMIP", " RMIV", "SILD", "TPAP", "TRAB", "TRAC", "TYVA", "TYVP", "TYVS", "VENP", "VENT", "VPRV", "ZEMA", "ZYME", "ZYMF"                'do nothing            Case Else                Rows(iREMOVENONPAHIG).Delete            End Select    Next iREMOVENONPAHIG    Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableHOURSLOCK As PivotTable, objFieldHOURSLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableHOURSLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("GROUPER")    objFieldHOURSLOCK.Orientation = xlColumnField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("PHARMACIST_LOCK")    objFieldHOURSLOCK.Orientation = xlRowField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("LOCKING TIME")    objFieldHOURSLOCK.Orientation = xlDataField    objFieldHOURSLOCK.Function = xlSum    objFieldHOURSLOCK.NumberFormat = "0.00"    objTableHOURSLOCK.RowAxisLayout xlOutlineRow    objTableHOURSLOCK.ShowTableStyleColumnStripes = True    objTableHOURSLOCK.ShowTableStyleRowStripes = True    objTableHOURSLOCK.TableStyle2 = "PivotStyleMedium9"    Sheets("Sheet2").Select    Sheets("Sheet2").Name = "HOURS LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select        Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableQUANTLOCK As PivotTable, objFieldQUANTLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableQUANTLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("GROUPER")    objFieldQUANTLOCK.Orientation = xlColumnField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("PHARMACIST_LOCK")    objFieldQUANTLOCK.Orientation = xlRowField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("THERAPY TYPE")    objFieldQUANTLOCK.Orientation = xlDataField    objFieldQUANTLOCK.Function = xlCount    objFieldQUANTLOCK.NumberFormat = "0"    objTableQUANTLOCK.RowAxisLayout xlOutlineRow    objTableQUANTLOCK.ShowTableStyleColumnStripes = True    objTableQUANTLOCK.ShowTableStyleRowStripes = True    objTableQUANTLOCK.TableStyle2 = "PivotStyleMedium12"    Sheets("Sheet3").Select    Sheets("Sheet3").Name = "QUANTITY LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Dim objTableDOUBLESHIP As PivotTable, objFieldDOUBLESHIP As PivotField    ActiveWorkbook.Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Set objTableDOUBLESHIP = Sheets("NETWORK DOUBLE SHIPS").PivotTableWizard    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("PHARMACIST_LOCK")    objFieldDOUBLESHIP.Orientation = xlRowField    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("THERAPY TYPE")    objFieldDOUBLESHIP.Orientation = xlDataField    objFieldDOUBLESHIP.Function = xlCount    objFieldDOUBLESHIP.NumberFormat = "0"    objTableDOUBLESHIP.RowAxisLayout xlOutlineRow    objTableDOUBLESHIP.ShowTableStyleColumnStripes = True    objTableDOUBLESHIP.ShowTableStyleRowStripes = True    objTableDOUBLESHIP.TableStyle2 = "PivotStyleMedium11"    Sheets("Sheet4").Select    Sheets("Sheet4").Name = "DOUBLE SHIP QUANTITY"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("DOUBLE SHIP QUANTITY").Select    Sheets("DOUBLE SHIP QUANTITY").Move Before:=Sheets(3)    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst    Sheets("HOURS LOCKED").Select With Application        .ScreenUpdating = True        .Calculation = xlCalculationManual        .DisplayAlerts = True        .EnableEvents = True    End WithEnd Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Can you try posting you code again so it displays properly. There seems to be a bug with the forum that causes this sometime.

Dom
 
Upvote 0
Code: 
8/21/2012 14:50	8/22/2012	SPO	RMIP	PAH INJ	PAH PAH TRC  0.18333348/21/2012 15:07	8/22/2012	SPO	RMIP	PAH INJ	PAH PAH TRC  0.1833334
Code: 
Sub RSLDAILYHOURS()'' RSLDAILYHOURS MacroWith Application        .ScreenUpdating = False        .Calculation = xlCalculationManual        .DisplayAlerts = False        .EnableEvents = FalseEnd WithDim ws As Object    Dim LCell As Range    Application.Calculation = xlCalculationManual    For Each ws In ActiveWorkbook.Sheets    On Error Resume Next    ws.Activate    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)    LCell.Formula = UCase(LCell.Formula)    NextNext ws    ActiveSheet.Select    ActiveSheet.Name = "RPH DAILY PRODUCTIVITY"    Columns("D:D").Select    Selection.Copy    Columns("E:E").Select    ActiveSheet.Paste    Columns("F:F").Select    ActiveSheet.Paste    Columns("G:G").Select    ActiveSheet.Paste    Application.CutCopyMode = False    Range("E1").Select    ActiveCell.FormulaR1C1 = "GROUPER"    Range("F1").Select    ActiveCell.FormulaR1C1 = "TRC"    Range("G1").Select    ActiveCell.FormulaR1C1 = "LOCKING TIME"    Range("A1").SelectColumns("E:E").SelectSelection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYME", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVS", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENC", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENT", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCR", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LETA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="SILD", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TPAP", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAB", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVC", Replacement:="REVA SUSP", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="F100", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAC", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPOP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FL50", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMO", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIV", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ALDZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ELAP", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VPRV", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LUMZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="MYOZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="BERT", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FZYR", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ARAL", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGF", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="HIZA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLBO", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GLSA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZEMA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ACTP", Replacement:="ACTI", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("F:F").SelectSelection.Replace What:="ACTP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="PAH TRAC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("G:G").SelectSelection.Replace What:="ACTP", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Columns("A:H").Select    Selection.Columns.AutoFit    Range("A1").Select    Sheets("RPH DAILY PRODUCTIVITY").Select    Sheets("RPH DAILY PRODUCTIVITY").Copy After:=Sheets(1)    Sheets("RPH DAILY PRODUCTIVITY (2)").Select    Sheets("RPH DAILY PRODUCTIVITY (2)").Name = "NETWORK DOUBLE SHIPS"    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select  Dim CelDS As Range, RngDS As Range, iDS As Long  Set RngDS = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS = RngDS.Count To 1 Step -1     If RngDS(iDS).Value = "PAH TRC" _            Then RngDS(iDS).EntireRow.Delete  Next iDS  Dim CelDS2 As Range, RngDS2 As Range, iDS2 As Long  Set RngDS2 = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS2 = RngDS2.Count To 1 Step -1     If RngDS2(iDS2).Value = "IG TRC" _            Then RngDS2(iDS2).EntireRow.Delete  Next iDS2    Sheets("RPH DAILY PRODUCTIVITY").Select    Dim CelREMOVENONPAHIG As Range, RngREMOVENONPAHIG As Range, iREMOVENONPAHIG As Long    Set RngREMOVENONPAHIG = Columns("D").SpecialCells(xlConstants, xlTextValues)    For iREMOVENONPAHIG = RngREMOVENONPAHIG.Count To 2 Step -1        Select Case Range("D" & iREMOVENONPAHIG).Value            Case "ACTP", "ADCC", "ADCR", "ALDZ", "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "ELAP", "FL50", "F100", "FABF", "FABZ", "FLBO", "FPAP", "FZYR", "GAMC", "GAMS", "GGIV", "GGSC", "GKIV", "GKSC", "GLSA", "GMPX", "HIZA", "IVGB", "IVGF", "IVGP", "IVGS", "LETA", "LUMZ", "MYOZ", "NGLF", "NGLZ", "REMO", "REMP", "REVA", "REVC", "RMIP", " RMIV", "SILD", "TPAP", "TRAB", "TRAC", "TYVA", "TYVP", "TYVS", "VENP", "VENT", "VPRV", "ZEMA", "ZYME", "ZYMF"                'do nothing            Case Else                Rows(iREMOVENONPAHIG).Delete            End Select    Next iREMOVENONPAHIG    Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableHOURSLOCK As PivotTable, objFieldHOURSLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableHOURSLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("GROUPER")    objFieldHOURSLOCK.Orientation = xlColumnField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("PHARMACIST_LOCK")    objFieldHOURSLOCK.Orientation = xlRowField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("LOCKING TIME")    objFieldHOURSLOCK.Orientation = xlDataField    objFieldHOURSLOCK.Function = xlSum    objFieldHOURSLOCK.NumberFormat = "0.00"    objTableHOURSLOCK.RowAxisLayout xlOutlineRow    objTableHOURSLOCK.ShowTableStyleColumnStripes = True    objTableHOURSLOCK.ShowTableStyleRowStripes = True    objTableHOURSLOCK.TableStyle2 = "PivotStyleMedium9"    Sheets("Sheet2").Select    Sheets("Sheet2").Name = "HOURS LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select        Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableQUANTLOCK As PivotTable, objFieldQUANTLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableQUANTLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("GROUPER")    objFieldQUANTLOCK.Orientation = xlColumnField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("PHARMACIST_LOCK")    objFieldQUANTLOCK.Orientation = xlRowField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("THERAPY TYPE")    objFieldQUANTLOCK.Orientation = xlDataField    objFieldQUANTLOCK.Function = xlCount    objFieldQUANTLOCK.NumberFormat = "0"    objTableQUANTLOCK.RowAxisLayout xlOutlineRow    objTableQUANTLOCK.ShowTableStyleColumnStripes = True    objTableQUANTLOCK.ShowTableStyleRowStripes = True    objTableQUANTLOCK.TableStyle2 = "PivotStyleMedium12"    Sheets("Sheet3").Select    Sheets("Sheet3").Name = "QUANTITY LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Dim objTableDOUBLESHIP As PivotTable, objFieldDOUBLESHIP As PivotField    ActiveWorkbook.Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Set objTableDOUBLESHIP = Sheets("NETWORK DOUBLE SHIPS").PivotTableWizard    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("PHARMACIST_LOCK")    objFieldDOUBLESHIP.Orientation = xlRowField    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("THERAPY TYPE")    objFieldDOUBLESHIP.Orientation = xlDataField    objFieldDOUBLESHIP.Function = xlCount    objFieldDOUBLESHIP.NumberFormat = "0"    objTableDOUBLESHIP.RowAxisLayout xlOutlineRow    objTableDOUBLESHIP.ShowTableStyleColumnStripes = True    objTableDOUBLESHIP.ShowTableStyleRowStripes = True    objTableDOUBLESHIP.TableStyle2 = "PivotStyleMedium11"    Sheets("Sheet4").Select    Sheets("Sheet4").Name = "DOUBLE SHIP QUANTITY"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("DOUBLE SHIP QUANTITY").Select    Sheets("DOUBLE SHIP QUANTITY").Move Before:=Sheets(3)    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst    Sheets("HOURS LOCKED").Select With Application        .ScreenUpdating = True        .Calculation = xlCalculationManual        .DisplayAlerts = True        .EnableEvents = True    End WithEnd Sub
 
Upvote 0
Code: 
Sub RSLDAILYHOURS()'' RSLDAILYHOURS MacroWith Application        .ScreenUpdating = False        .Calculation = xlCalculationManual        .DisplayAlerts = False        .EnableEvents = FalseEnd WithDim ws As Object    Dim LCell As Range    Application.Calculation = xlCalculationManual    For Each ws In ActiveWorkbook.Sheets    On Error Resume Next    ws.Activate    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)    LCell.Formula = UCase(LCell.Formula)    NextNext ws    ActiveSheet.Select    ActiveSheet.Name = "RPH DAILY PRODUCTIVITY"    Columns("D:D").Select    Selection.Copy    Columns("E:E").Select    ActiveSheet.Paste    Columns("F:F").Select    ActiveSheet.Paste    Columns("G:G").Select    ActiveSheet.Paste    Application.CutCopyMode = False    Range("E1").Select    ActiveCell.FormulaR1C1 = "GROUPER"    Range("F1").Select    ActiveCell.FormulaR1C1 = "TRC"    Range("G1").Select    ActiveCell.FormulaR1C1 = "LOCKING TIME"    Range("A1").SelectColumns("E:E").SelectSelection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYME", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TYVS", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENC", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENT", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VENP", Replacement:="PAH INH", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCR", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ADCC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LETA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVA", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="SILD", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TPAP", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAB", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="TRAC", Replacement:="PAH ORALS", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REVC", Replacement:="REVA SUSP", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="F100", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAC", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="EPOP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FL50", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FPAP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMO", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="REMP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIP", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="RMIV", Replacement:="PAH INJ", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ALDZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ELAP", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FABZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="VPRV", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="LUMZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="MYOZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="NGLZ", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMF", Replacement:="ZYMES", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="BERT", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FZYR", Replacement:="HAE", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ARAL", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGF", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GKSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="HIZA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IVGS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLBO", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GAMS", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GLSA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GGSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZEMA", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="ALPHA-IG", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ACTP", Replacement:="ACTI", LookAt:=xlPart, _        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Selection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("F:F").SelectSelection.Replace What:="ACTP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="PAH TRAC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="PAH TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="IG TRC", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseColumns("G:G").SelectSelection.Replace What:="ACTP", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="0.05", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="0.1666667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="0.0416667", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="0.1083334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="0.1833334", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="0.15", LookAt:=xlPart _        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _        ReplaceFormat:=False    Columns("A:H").Select    Selection.Columns.AutoFit    Range("A1").Select    Sheets("RPH DAILY PRODUCTIVITY").Select    Sheets("RPH DAILY PRODUCTIVITY").Copy After:=Sheets(1)    Sheets("RPH DAILY PRODUCTIVITY (2)").Select    Sheets("RPH DAILY PRODUCTIVITY (2)").Name = "NETWORK DOUBLE SHIPS"    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select  Dim CelDS As Range, RngDS As Range, iDS As Long  Set RngDS = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS = RngDS.Count To 1 Step -1     If RngDS(iDS).Value = "PAH TRC" _            Then RngDS(iDS).EntireRow.Delete  Next iDS  Dim CelDS2 As Range, RngDS2 As Range, iDS2 As Long  Set RngDS2 = Columns("F").SpecialCells(xlConstants, xlTextValues)  For iDS2 = RngDS2.Count To 1 Step -1     If RngDS2(iDS2).Value = "IG TRC" _            Then RngDS2(iDS2).EntireRow.Delete  Next iDS2    Sheets("RPH DAILY PRODUCTIVITY").Select    Dim CelREMOVENONPAHIG As Range, RngREMOVENONPAHIG As Range, iREMOVENONPAHIG As Long    Set RngREMOVENONPAHIG = Columns("D").SpecialCells(xlConstants, xlTextValues)    For iREMOVENONPAHIG = RngREMOVENONPAHIG.Count To 2 Step -1        Select Case Range("D" & iREMOVENONPAHIG).Value            Case "ACTP", "ADCC", "ADCR", "ALDZ", "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "ELAP", "FL50", "F100", "FABF", "FABZ", "FLBO", "FPAP", "FZYR", "GAMC", "GAMS", "GGIV", "GGSC", "GKIV", "GKSC", "GLSA", "GMPX", "HIZA", "IVGB", "IVGF", "IVGP", "IVGS", "LETA", "LUMZ", "MYOZ", "NGLF", "NGLZ", "REMO", "REMP", "REVA", "REVC", "RMIP", " RMIV", "SILD", "TPAP", "TRAB", "TRAC", "TYVA", "TYVP", "TYVS", "VENP", "VENT", "VPRV", "ZEMA", "ZYME", "ZYMF"                'do nothing            Case Else                Rows(iREMOVENONPAHIG).Delete            End Select    Next iREMOVENONPAHIG    Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableHOURSLOCK As PivotTable, objFieldHOURSLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableHOURSLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("GROUPER")    objFieldHOURSLOCK.Orientation = xlColumnField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("PHARMACIST_LOCK")    objFieldHOURSLOCK.Orientation = xlRowField    Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("LOCKING TIME")    objFieldHOURSLOCK.Orientation = xlDataField    objFieldHOURSLOCK.Function = xlSum    objFieldHOURSLOCK.NumberFormat = "0.00"    objTableHOURSLOCK.RowAxisLayout xlOutlineRow    objTableHOURSLOCK.ShowTableStyleColumnStripes = True    objTableHOURSLOCK.ShowTableStyleRowStripes = True    objTableHOURSLOCK.TableStyle2 = "PivotStyleMedium9"    Sheets("Sheet2").Select    Sheets("Sheet2").Name = "HOURS LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select        Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Dim objTableQUANTLOCK As PivotTable, objFieldQUANTLOCK As PivotField    ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select    Range("A1").Select    Set objTableQUANTLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("GROUPER")    objFieldQUANTLOCK.Orientation = xlColumnField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("PHARMACIST_LOCK")    objFieldQUANTLOCK.Orientation = xlRowField    Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("THERAPY TYPE")    objFieldQUANTLOCK.Orientation = xlDataField    objFieldQUANTLOCK.Function = xlCount    objFieldQUANTLOCK.NumberFormat = "0"    objTableQUANTLOCK.RowAxisLayout xlOutlineRow    objTableQUANTLOCK.ShowTableStyleColumnStripes = True    objTableQUANTLOCK.ShowTableStyleRowStripes = True    objTableQUANTLOCK.TableStyle2 = "PivotStyleMedium12"    Sheets("Sheet3").Select    Sheets("Sheet3").Name = "QUANTITY LOCKED"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Dim objTableDOUBLESHIP As PivotTable, objFieldDOUBLESHIP As PivotField    ActiveWorkbook.Sheets("NETWORK DOUBLE SHIPS").Select    Range("A1").Select    Set objTableDOUBLESHIP = Sheets("NETWORK DOUBLE SHIPS").PivotTableWizard    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("PHARMACIST_LOCK")    objFieldDOUBLESHIP.Orientation = xlRowField    Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("THERAPY TYPE")    objFieldDOUBLESHIP.Orientation = xlDataField    objFieldDOUBLESHIP.Function = xlCount    objFieldDOUBLESHIP.NumberFormat = "0"    objTableDOUBLESHIP.RowAxisLayout xlOutlineRow    objTableDOUBLESHIP.ShowTableStyleColumnStripes = True    objTableDOUBLESHIP.ShowTableStyleRowStripes = True    objTableDOUBLESHIP.TableStyle2 = "PivotStyleMedium11"    Sheets("Sheet4").Select    Sheets("Sheet4").Name = "DOUBLE SHIP QUANTITY"    ActiveWorkbook.ShowPivotTableFieldList = False    Range("A1").Select    Sheets("DOUBLE SHIP QUANTITY").Select    Sheets("DOUBLE SHIP QUANTITY").Move Before:=Sheets(3)    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst    Sheets("HOURS LOCKED").Select With Application        .ScreenUpdating = True        .Calculation = xlCalculationManual        .DisplayAlerts = True        .EnableEvents = True    End WithEnd Sub
 
Upvote 0
i dont know why its doing this i have posted it a few times, best I can do.
 
Upvote 0
Sub RSLDAILYHOURS()'' RSLDAILYHOURS MacroWith Application .ScreenUpdating = False .Calculation = xlCalculationManual .DisplayAlerts = False .EnableEvents = FalseEnd WithDim ws As Object Dim LCell As Range Application.Calculation = xlCalculationManual For Each ws In ActiveWorkbook.Sheets On Error Resume Next ws.Activate For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues) LCell.Formula = UCase(LCell.Formula) NextNext ws ActiveSheet.Select ActiveSheet.Name = "RPH DAILY PRODUCTIVITY" Columns("D:D").Select Selection.Copy Columns("E:E").Select ActiveSheet.Paste Columns("F:F").Select ActiveSheet.Paste Columns("G:G").Select ActiveSheet.Paste Application.CutCopyMode = False Range("E1").Select ActiveCell.FormulaR1C1 = "GROUPER" Range("F1").Select ActiveCell.FormulaR1C1 = "TRC" Range("G1").Select ActiveCell.FormulaR1C1 = "LOCKING TIME" Range("A1").SelectColumns("E:E").SelectSelection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZYME", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TYVP", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TYVA", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TYVS", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="VENC", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="VENT", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="VENP", Replacement:="PAH INH", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ADCR", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ADCC", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="LETA", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="REVA", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="SILD", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TPAP", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TRAB", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="TRAC", Replacement:="PAH ORALS", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="REVC", Replacement:="REVA SUSP", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="F100", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="EPAC", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="EPAP", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="EPOP", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FL50", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FPAP", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="REMO", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="REMP", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="RMIP", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="RMIV", Replacement:="PAH INJ", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ALDZ", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ELAP", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FABF", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FABZ", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="VPRV", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="LUMZ", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="MYOZ", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="NGLF", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="NGLZ", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZYMF", Replacement:="ZYMES", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="BERT", Replacement:="HAE", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FZYR", Replacement:="HAE", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ARAL", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="IVGF", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GKIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GKSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="HIZA", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="IVGP", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="IVGS", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GGIV", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FLBO", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GAMC", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GAMS", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GLSA", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GGSC", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZEMA", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZIMP", Replacement:="ALPHA-IG", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ACTP", Replacement:="ACTI", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseColumns("F:F").SelectSelection.Replace What:="ACTP", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="PAH TRAC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="PAH TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="IG TRC", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="STUDY", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseColumns("G:G").SelectSelection.Replace What:="ACTP", Replacement:="0.05", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ADCC", Replacement:="0.1666667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ADCR", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ALDZ", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ARAL", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="BERT", Replacement:="0.05", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPAC", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPAP", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="EPOP", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ELAP", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FL50", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="F100", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FABF", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FABZ", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FLBO", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FPAP", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FZYR", Replacement:="0.05", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GAMC", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GAMS", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GGIV", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GGSC", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GKIV", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GKSC", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GLSA", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GMPX", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="HIZA", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGB", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGF", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGP", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IVGS", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="LETA", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="LUMZ", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="MYOZ", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="NGLF", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="NGLZ", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REMO", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REMP", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REVA", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="REVC", Replacement:="0.1666667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="RMIP", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="RMIV", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="SILD", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TPAP", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TRAB", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TRAC", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVA", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVP", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="TYVS", Replacement:="0.0416667", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VENP", Replacement:="0.1083334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VENT", Replacement:="0.1083334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="VPRV", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZEMA", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYME", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYMF", Replacement:="0.1833334", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="AGLP", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="AGLS", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="A100", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="A208", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="GCST", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZIMP", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="ZYMS", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="FLSD", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseSelection.Replace What:="IGST", Replacement:="0.15", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns("A:H").Select Selection.Columns.AutoFit Range("A1").Select Sheets("RPH DAILY PRODUCTIVITY").Select Sheets("RPH DAILY PRODUCTIVITY").Copy After:=Sheets(1) Sheets("RPH DAILY PRODUCTIVITY (2)").Select Sheets("RPH DAILY PRODUCTIVITY (2)").Name = "NETWORK DOUBLE SHIPS" Range("A1").Select Sheets("NETWORK DOUBLE SHIPS").Select Dim CelDS As Range, RngDS As Range, iDS As Long Set RngDS = Columns("F").SpecialCells(xlConstants, xlTextValues) For iDS = RngDS.Count To 1 Step -1 If RngDS(iDS).Value = "PAH TRC" _ Then RngDS(iDS).EntireRow.Delete Next iDS Dim CelDS2 As Range, RngDS2 As Range, iDS2 As Long Set RngDS2 = Columns("F").SpecialCells(xlConstants, xlTextValues) For iDS2 = RngDS2.Count To 1 Step -1 If RngDS2(iDS2).Value = "IG TRC" _ Then RngDS2(iDS2).EntireRow.Delete Next iDS2 Sheets("RPH DAILY PRODUCTIVITY").Select Dim CelREMOVENONPAHIG As Range, RngREMOVENONPAHIG As Range, iREMOVENONPAHIG As Long Set RngREMOVENONPAHIG = Columns("D").SpecialCells(xlConstants, xlTextValues) For iREMOVENONPAHIG = RngREMOVENONPAHIG.Count To 2 Step -1 Select Case Range("D" & iREMOVENONPAHIG).Value Case "ACTP", "ADCC", "ADCR", "ALDZ", "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "ELAP", "FL50", "F100", "FABF", "FABZ", "FLBO", "FPAP", "FZYR", "GAMC", "GAMS", "GGIV", "GGSC", "GKIV", "GKSC", "GLSA", "GMPX", "HIZA", "IVGB", "IVGF", "IVGP", "IVGS", "LETA", "LUMZ", "MYOZ", "NGLF", "NGLZ", "REMO", "REMP", "REVA", "REVC", "RMIP", " RMIV", "SILD", "TPAP", "TRAB", "TRAC", "TYVA", "TYVP", "TYVS", "VENP", "VENT", "VPRV", "ZEMA", "ZYME", "ZYMF" 'do nothing Case Else Rows(iREMOVENONPAHIG).Delete End Select Next iREMOVENONPAHIG Sheets("RPH DAILY PRODUCTIVITY").Select Range("A1").Select Dim objTableHOURSLOCK As PivotTable, objFieldHOURSLOCK As PivotField ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select Range("A1").Select Set objTableHOURSLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("GROUPER") objFieldHOURSLOCK.Orientation = xlColumnField Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("PHARMACIST_LOCK") objFieldHOURSLOCK.Orientation = xlRowField Set objFieldHOURSLOCK = objTableHOURSLOCK.PivotFields("LOCKING TIME") objFieldHOURSLOCK.Orientation = xlDataField objFieldHOURSLOCK.Function = xlSum objFieldHOURSLOCK.NumberFormat = "0.00" objTableHOURSLOCK.RowAxisLayout xlOutlineRow objTableHOURSLOCK.ShowTableStyleColumnStripes = True objTableHOURSLOCK.ShowTableStyleRowStripes = True objTableHOURSLOCK.TableStyle2 = "PivotStyleMedium9" Sheets("Sheet2").Select Sheets("Sheet2").Name = "HOURS LOCKED" ActiveWorkbook.ShowPivotTableFieldList = False Range("A1").Select Sheets("RPH DAILY PRODUCTIVITY").Select Range("A1").Select Dim objTableQUANTLOCK As PivotTable, objFieldQUANTLOCK As PivotField ActiveWorkbook.Sheets("RPH DAILY PRODUCTIVITY").Select Range("A1").Select Set objTableQUANTLOCK = Sheets("RPH DAILY PRODUCTIVITY").PivotTableWizard Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("GROUPER") objFieldQUANTLOCK.Orientation = xlColumnField Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("PHARMACIST_LOCK") objFieldQUANTLOCK.Orientation = xlRowField Set objFieldQUANTLOCK = objTableQUANTLOCK.PivotFields("THERAPY TYPE") objFieldQUANTLOCK.Orientation = xlDataField objFieldQUANTLOCK.Function = xlCount objFieldQUANTLOCK.NumberFormat = "0" objTableQUANTLOCK.RowAxisLayout xlOutlineRow objTableQUANTLOCK.ShowTableStyleColumnStripes = True objTableQUANTLOCK.ShowTableStyleRowStripes = True objTableQUANTLOCK.TableStyle2 = "PivotStyleMedium12" Sheets("Sheet3").Select Sheets("Sheet3").Name = "QUANTITY LOCKED" ActiveWorkbook.ShowPivotTableFieldList = False Range("A1").Select Sheets("NETWORK DOUBLE SHIPS").Select Range("A1").Select Dim objTableDOUBLESHIP As PivotTable, objFieldDOUBLESHIP As PivotField ActiveWorkbook.Sheets("NETWORK DOUBLE SHIPS").Select Range("A1").Select Set objTableDOUBLESHIP = Sheets("NETWORK DOUBLE SHIPS").PivotTableWizard Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("PHARMACIST_LOCK") objFieldDOUBLESHIP.Orientation = xlRowField Set objFieldDOUBLESHIP = objTableDOUBLESHIP.PivotFields("THERAPY TYPE") objFieldDOUBLESHIP.Orientation = xlDataField objFieldDOUBLESHIP.Function = xlCount objFieldDOUBLESHIP.NumberFormat = "0" objTableDOUBLESHIP.RowAxisLayout xlOutlineRow objTableDOUBLESHIP.ShowTableStyleColumnStripes = True objTableDOUBLESHIP.ShowTableStyleRowStripes = True objTableDOUBLESHIP.TableStyle2 = "PivotStyleMedium11" Sheets("Sheet4").Select Sheets("Sheet4").Name = "DOUBLE SHIP QUANTITY" ActiveWorkbook.ShowPivotTableFieldList = False Range("A1").Select Sheets("DOUBLE SHIP QUANTITY").Select Sheets("DOUBLE SHIP QUANTITY").Move Before:=Sheets(3) ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("HOURS LOCKED").Select With Application .ScreenUpdating = True .Calculation = xlCalculationManual .DisplayAlerts = True .EnableEvents = True End WithEnd Sub
 
Upvote 0
Sorry but I can't even begin to see what that's doing. Are you using Chrome?

Dom
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
How to loop to find and replace with the specific cells
Replies
2
Views
252
Linda Lo
L
J
  • Solved
Assistance with excel macro
Replies
3
Views
214
dreid1011
dreid1011
N
  • Question
Not understanding loop in vba
Replies
7
Views
250
jolivanes
jolivanes
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
247
dwalc
D
R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
207
rsulliva
R

Forum statistics

Threads
1,221,560
Messages
6,160,493
Members
451,653
Latest member
agata

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top