rkol297
Board Regular
- Joined
- Nov 12, 2010
- Messages
- 131
- Office Version
- 365
- 2019
- Platform
- Windows
Can anyone help split this large macro into individual macros that would run in succession as indicated by the break points I've indicated within? When I break it up I am receiving errors when the Pivot table code starts to run.????
Thanks,
Thanks,
Code:
Sub RSLDashboardtesting()
Dim APPSPD As Worksheet
-------TURN UPDATING OFF_____
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.EnableEvents = False
End With
_____FIRSTBREAK____
'START SHEET SETUP
Dim ws As Object
Dim LCell As Range
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)
Next
Next ws
Columns("E:E").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Columns("T:T").Select
Selection.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:="TYV2", 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:="A-P-T", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="TOBI", Replacement:="A-P-T", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="PUL", Replacement:="A-P-T", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Range("U1").Select
ActiveSheet.Paste
Columns("U:U").Select
Selection.Replace What:="PAH INH", Replacement:="PAH TRC", lookat:= _
xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="PAH ORALS", Replacement:="PAH TRC", lookat:= _
xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="REVA SUSP", Replacement:="PAH TRC", _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="PAH INJ", Replacement:="PAH TRC", lookat:= _
xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ZYMES", Replacement:="IG TRC", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="HAE", Replacement:="IG TRC", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ALPHA-IG", Replacement:="IG TRC", lookat:=xlPart _
, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ALPHA-IG", Replacement:="IG TRC", lookat:=xlPart _
, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="A-P-T", Replacement:="IG TRC", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("T1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "GROUPER"
Range("U1").Select
ActiveCell.FormulaR1C1 = "TRC"
Columns("T:T").Select
Selection.Copy
Range("V1").Select
ActiveSheet.Paste
Range("V1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "HRS TO LOCK"
Columns("V:V").Select
Selection.Replace What:="PAH INH", Replacement:="0.075", lookat:=xlPart _
, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="PAH ORALS", Replacement:="0.042", lookat:=xlPart _
, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="REVA SUSP", Replacement:=".25", lookat _
:=xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="PAH INJ", Replacement:=".167", lookat:= _
xlPart, searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ZYMES", Replacement:=".183", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="HAE", Replacement:=".05", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ALPHA-IG", Replacement:=".183", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="A-P-T", Replacement:=".033", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:V1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Columns.AutoFit
Range("A1").Select
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.Columns.AutoFit
Columns("F:F").Select
Selection.Columns.AutoFit
Columns("A:A").Select
Selection.Columns.AutoFit
Columns("L:L").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("L1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("K:K").Select
Selection.Columns.AutoFit
Range("K1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.ScrollColumn = 8
Columns("P:P").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("P1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("Q:Q").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("Q1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("R:R").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("R1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("S:S").Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("S1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("T:T").Select
Selection.Columns.AutoFit
Columns("U:U").Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 9
Columns("V:V").Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("D:D").Select
Selection.Columns.AutoFit
Selection.ColumnWidth = 26.29
Columns("J:J").Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "ON HOLD"
Application.Run "CallCheckEntry"
Range("F1").Select
ActiveCell.FormulaR1C1 = "RX HOME ID #"
Application.Run "CallCheckEntry"
Range("J1").Select
ActiveCell.FormulaR1C1 = "MEDCO MAIL OR AOB"
Application.Run "CallCheckEntry"
Range("K1").Select
ActiveCell.FormulaR1C1 = "MEDCO ID #"
Application.Run "CallCheckEntry"
Range("L1").Select
ActiveCell.FormulaR1C1 = "SC NOTE"
Application.Run "CallCheckEntry"
Range("M1").Select
ActiveCell.FormulaR1C1 = "WHY NO SC NOTE"
Application.Run "CallCheckEntry"
Range("R1").Select
ActiveCell.FormulaR1C1 = "FRONT SCREEN SB #"
Application.Run "CallCheckEntry"
Range("A1").Select
ActiveSheet.Select
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range("W1").Select
ActiveCell.FormulaR1C1 = "DAY"
ActiveSheet.Select
Dim X As Long, DataLastRow As Long
Const DataStartRow As Long = 2
DataLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = DataStartRow To DataLastRow
If Weekday(Cells(X, "A").Value) = 7 Then
Cells(X, "W").Value = "SATURDAY SHIP"
ElseIf Weekday(Cells(X, "A").Value) = 1 Then
Cells(X, "W").Value = "SUNDAY SHIP"
Else
Select Case Cells(X, "A").Value - Date
Case Is < 0
Cells(X, "W").Value = "PREVIOUS SHIP"
Case 0
Cells(X, "W").Value = "SAME DAY"
Case 1 - 2 * (Weekday(Date) = 6)
Cells(X, "W").Value = "NEXT DAY"
Case Else
Cells(X, "W").Value = "FUTURE SHIP"
End Select
End If
Next
Dim mC As Long, DataLastRowmC As Long
Const DataStartRowmC As Long = 2
DataLastRowmC = Cells(Rows.Count, "S").End(xlUp).Row
For mC = DataStartRowmC To DataLastRowmC
If Cells(mC, "S").Value = "NJ MCAID" Then
Cells(mC, "S").Font.ColorIndex = 1
Cells(mC, "S").Interior.ColorIndex = 27
Cells(mC, "S").Font.Bold = True
ElseIf Cells(mC, "S").Value = "NY MCAID" Then
Cells(mC, "S").Font.ColorIndex = 1
Cells(mC, "S").Interior.ColorIndex = 27
Cells(mC, "S").Font.Bold = True
ElseIf Cells(mC, "S").Value = "MCAID" Then
Cells(mC, "S").Font.ColorIndex = 1
Cells(mC, "S").Interior.ColorIndex = 27
Cells(mC, "S").Font.Bold = True
ElseIf Cells(mC, "S").Value = "PAMA" Then
Cells(mC, "S").Font.ColorIndex = 1
Cells(mC, "S").Interior.ColorIndex = 27
Cells(mC, "S").Font.Bold = True
End If
Next
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
Range("B1").Select
Selection.NumberFormat = "General"
Range("B2").Select
Dim SD As Long, DataLastRowSD As Long
Const DataStartRowSD As Long = 2
DataLastRowSD = Cells(Rows.Count, "B").End(xlUp).Row
For SD = DataStartRoSD To DataLastRowSD
Select Case Cells(SD, "B").Value - Date
Case 0: Cells(SD, "B").Font.ColorIndex = 2
Cells(SD, "B").Interior.ColorIndex = 3
Cells(SD, "B").Font.Bold = True
Cells(SD, "A").Font.ColorIndex = 2
Cells(SD, "A").Interior.ColorIndex = 3
Cells(SD, "A").Font.Bold = True
End Select
Next
Range("A1:W1").Select
ActiveWindow.SmallScroll ToRight:=1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("A1:W1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:W1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Rows("2:2").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Range("A1").Select
Range("A1:W1").Select
Selection.AutoFilter
Range("A1").Select
ActiveWindow.ScrollColumn = 10
Columns("X:X").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range("A1").Select
Range("A1").Select
ActiveWindow.ScrollColumn = 10
Columns("W:W").Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 1
Range("A1").Select
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Select
ActiveSheet.Name = "MEDCO RSL"
Sheets(1).Select
ActiveSheet.Name = "AOB RSL"
ActiveSheet.Select
Sheets("AOB RSL").Select
Sheets("AOB RSL").Copy Before:=Sheets(1)
Sheets("AOB RSL (2)").Select
Sheets("AOB RSL (2)").Name = "NO SC NOTES"
Sheets("NO SC NOTES").Select
Sheets("NO SC NOTES").Copy Before:=Sheets(1)
Sheets("NO SC NOTES (2)").Select
Sheets("NO SC NOTES (2)").Name = "NETWORK DBL SHIPS"
Sheets("NETWORK DBL SHIPS").Select
Sheets("NETWORK DBL SHIPS").Copy Before:=Sheets(1)
Sheets("NETWORK DBL SHIPS (2)").Select
Sheets("NETWORK DBL SHIPS (2)").Name = "HOLDS REPORT"
Sheets("HOLDS REPORT").Select
Sheets("HOLDS REPORT").Copy Before:=Sheets(1)
Sheets("HOLDS REPORT (2)").Select
Sheets("HOLDS REPORT (2)").Name = "RLS HOURLY REPORT"
Sheets("RLS HOURLY REPORT").Select
Range("A1").Select
Sheets("NETWORK DBL SHIPS").Delete
'END SHEET SETUP
_____SECOND BREAK____
'START OF CODING FOR SPECIFIC FUNCTIONS
'SORTS HOLD SHEET LOOKING FOR ANSWER N IN COLUMN C
Sheets("HOLDS REPORT").Select
Dim CelH As Range, RngH As Range, iH As Long
Set RngH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iH = RngH.Count To 1 Step -1
If RngH(iH).Value = "N" _
Or RngH(iH).Value = "" _
Then RngH(iH).EntireRow.Delete
Next iH
Range("A1").Select
'SORTS FOR NO SC NOTES BY LOOKING AT COLUMN J AND REMOVING ANY AOB
Sheets("NO SC NOTES").Select
Dim CelSC As Range, RngSC As Range, iSC As Long
Set RngSC = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iSC = RngSC.Count To 1 Step -1
If RngSC(iSC).Value = "AOB" _
Then RngSC(iSC).EntireRow.Delete
Next iSC
'SORTS FOR NO SC NOTE BY LOOKING AT COLUMN J AND SC NOTE ANSWER YES IN COLUMN L
Range("A1").Select
Dim CelSCM As Range, RngSCM As Range, iSCM As Long
Set RngSCM = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iSCM = RngSCM.Count To 1 Step -1
If RngSCM(iSCM).Value = "MEDCO MAIL" _
And RngSCM(iSCM).Offset(0, 2).Value = "YES" _
Then RngSCM(iSCM).EntireRow.Delete
Next iSCM
'DELETES ROW IF COLUMN C IS YES OR BLANK SO THAT TO SORT ALL ORDERS THAT ARE NOT ON HOLD
Range("A1").Select
Dim CelSCH As Range, RngSCH As Range, iSCH As Long
Set RngSCH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iSCH = RngSCH.Count To 1 Step -1
If RngSCH(iSCH).Value = "Y" _
Or RngSCH(iSCH).Value = "" _
Then RngSCH(iSCH).EntireRow.Delete
Next iSCH
Range("A1").Select
'REMOVES MEDCO MAIL FROM COLUMN J
Sheets("AOB RSL").Select
Dim CelAOB As Range, RngAOB As Range, iAOB As Long
Set RngAOB = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iAOB = RngAOB.Count To 1 Step -1
If RngAOB(iAOB).Value = "MEDCO MAIL" _
Then RngAOB(iAOB).EntireRow.Delete
Next iAOB
'REMOVES HOLDS AND BLANKS FROM COLUMN C
Range("A1").Select
Dim CelAOBH As Range, RngAOBH As Range, iAOBH As Long
Set RngAOBH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iAOBH = RngAOBH.Count To 1 Step -1
If RngAOBH(iAOBH).Value = "Y" _
Or RngAOBH(iAOBH).Value = "" _
Then RngAOBH(iAOBH).EntireRow.Delete
Next iAOBH
'REMOVES ALL OTHER THERAPIES EXCEPT PAH TRC AND IG TRC FROM COLUMN U
Dim CelAOBPAHIGONLY As Range, RngAOBPAHIGONLY As Range, iAOBPAHIGONLY As Long
Set RngAOBPAHIGONLY = Columns("U").SpecialCells(xlConstants, xlTextValues)
For iAOBPAHIGONLY = RngAOBPAHIGONLY.Count To 2 Step -1
Select Case Range("U" & iAOBPAHIGONLY).Value
Case "PAH TRC", "IG TRC"
'do nothing
Case Else
Rows(iAOBPAHIGONLY).Delete
End Select
Next iAOBPAHIGONLY
'REMOVES HOLDS AND BLANKS FROM COLUMN C ON MEDCO RSL
Sheets("MEDCO RSL").Select
Dim CelMEDCO As Range, RngMEDCO As Range, iMEDCO As Long
Set RngMEDCO = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iMEDCO = RngMEDCO.Count To 1 Step -1
If RngMEDCO(iMEDCO).Value = "AOB" _
Then RngMEDCO(iMEDCO).EntireRow.Delete
Next iMEDCO
'REMOVES HOLDS AND BLANKS FROM COLUMN C ON MEDCO RSL
Range("A1").Select
Dim CelMEDCOH As Range, RngMEDCOH As Range, iMEDCOH As Long
Set RngMEDCOH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iMEDCOH = RngMEDCOH.Count To 1 Step -1
If RngMEDCOH(iMEDCOH).Value = "Y" _
Or RngMEDCOH(iMEDCOH).Value = "" _
Then RngMEDCOH(iMEDCOH).EntireRow.Delete
Next iMEDCOH
'REMOVES ORDERS WITHOUT SC NOTES
Range("A1").Select
Dim CelMEDCOSC As Range, RngMEDCOSC As Range, iMEDCOSC As Long
Set RngMEDCOSC = Columns("L").SpecialCells(xlConstants, xlTextValues)
For iMEDCOSC = RngMEDCOSC.Count To 1 Step -1
If RngMEDCOSC(iMEDCOSC).Value = "NO" _
Or RngMEDCOSC(iMEDCOSC).Value = "" _
Then RngMEDCOSC(iMEDCOSC).EntireRow.Delete
Next iMEDCOSC
'COPIES THE MEDCO RSL TO SETUP IG MEDCO AND PAH MEDCO TABS
Sheets("MEDCO RSL").Select
Sheets("MEDCO RSL").Copy After:=Sheets("MEDCO RSL")
Sheets("MEDCO RSL (2)").Select
Sheets("MEDCO RSL (2)").Name = "IG MEDCO"
Sheets("MEDCO RSL").Select
Sheets("MEDCO RSL").Copy After:=Sheets("IG MEDCO")
Sheets("MEDCO RSL (2)").Select
Sheets("MEDCO RSL (2)").Name = "PAH MEDCO"
'REMOVES -0 FILLS FROM MEDCO RSL COLUMN I
Sheets("MEDCO RSL").Select
Dim CelMEDCO0F As Range, RngMEDCO0F As Range, iMEDCO0F As Long
Set RngMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iMEDCO0F = RngMEDCO0F.Count To 1 Step -1
If RngMEDCO0F(iMEDCO0F).Value Like "*-0*" _
Then RngMEDCO0F(iMEDCO0F).EntireRow.Delete
Next iMEDCO0F
'REMOVES VARIABLE FILLS FROM MEDCO RSL USING THERAPY TYPE IN COLUMN E
Dim CelMEDCOVF As Range, RngMEDCOVF As Range, iMEDCOVF As Long
Set RngMEDCOVF = Columns("E").SpecialCells(xlConstants, xlTextValues)
For iMEDCOVF = RngMEDCOVF.Count To 1 Step -1
If RngMEDCOVF(iMEDCOVF).Value = "ARAL, BERT, EPAC, EPAP, EPOP, FL50, F100, GLSA, REMO, REMP, RMIP, RMIV, VENP, VENT, ZEMA, ZYME, ZYMF" _
Then RngMEDCOVF(iMEDCOVF).EntireRow.Delete
Next iMEDCOVF
'REMOVES ALL PAH THERAPY TYPES FROM IG MEDCO TAB BY DELETING ALL ROWS WITH PAH TRC IN COLUMN U
Sheets("IG MEDCO").Select
Dim CelIGMEDCOIGTRC As Range, RngIGMEDCOIGTRC As Range, iIGMEDCOIGTRC As Long
Set RngIGMEDCOIGTRC = Columns("U").SpecialCells(xlConstants, xlTextValues)
For iIGMEDCOIGTRC = RngIGMEDCOIGTRC.Count To 2 Step -1
If RngIGMEDCOIGTRC(iIGMEDCOIGTRC).Value = "PAH TRC" _
Then RngIGMEDCOIGTRC(iIGMEDCOIGTRC).EntireRow.Delete
Next iIGMEDCOIGTRC
'REMOVES ALL IG THERAPY TYPES FROM PAH MEDCO TAB BY DELETING ALL ROWS WITH IG TRC IN COLUMN U
Sheets("PAH MEDCO").Select
Dim CelPAHMEDCOPAHTRC As Range, RngPAHMEDCOPAHTRC As Range, iPAHMEDCOPAHTRC As Long
Set RngPAHMEDCOPAHTRC = Columns("U").SpecialCells(xlConstants, xlTextValues)
For iPAHMEDCOPAHTRC = RngPAHMEDCOPAHTRC.Count To 2 Step -1
If RngPAHMEDCOPAHTRC(iPAHMEDCOPAHTRC).Value = "IG TRC" _
Then RngPAHMEDCOPAHTRC(iPAHMEDCOPAHTRC).EntireRow.Delete
Next iPAHMEDCOPAHTRC
'REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES FOR IG MEDCO TAB
Sheets("IG MEDCO").Select
Dim CelIGMEDCO0F As Range, RngIGMEDCO0F As Range, iIGMEDCO0F As Long
Set RngIGMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iIGMEDCO0F = RngIGMEDCO0F.Count To 2 Step -1
Select Case Range("E" & iIGMEDCO0F).Value
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
'do nothing
Case Else
If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" _
Then Rows(iIGMEDCO0F).Delete
End Select
Next iIGMEDCO0F
'REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES FOR PAH MEDCO TAB
Sheets("PAH MEDCO").Select
Dim CelPAHMEDCO0F As Range, RngPAHMEDCO0F As Range, iPAHMEDCO0F As Long
Set RngPAHMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iPAHMEDCO0F = RngPAHMEDCO0F.Count To 2 Step -1
Select Case Range("E" & iPAHMEDCO0F).Value
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
'do nothing
Case Else
If Right(Range("I" & iPAHMEDCO0F).Value, 2) <> "-0" _
Then Rows(iPAHMEDCO0F).Delete
End Select
Next iPAHMEDCO0F
'REMOVES ALL PAH TRC AND IG TRC THERAPY TYPE TO REVEAL ONLY DOUBLE SHIP THERAPY TYPES
Sheets("RLS HOURLY REPORT").Select
Sheets("RLS HOURLY REPORT").Copy After:=Sheets(7)
Sheets("RLS HOURLY REPORT (2)").Select
Sheets("RLS HOURLY REPORT (2)").Name = "STATS DATA"
Sheets("RLS HOURLY REPORT").Select
Sheets("RLS HOURLY REPORT").Name = "NETWORK DBL SHIPS"
Dim CelNWD As Range, RngNWD As Range, iNWD As Long
Set RngNWD = Columns("U").SpecialCells(xlConstants, xlTextValues)
For iNWD = RngNWD.Count To 1 Step -1
If RngNWD(iNWD).Value = "PAH TRC" _
Or RngNWD(iNWD).Value = "IG TRC" _
Then RngNWD(iNWD).EntireRow.Delete
Next iNWD
'REARRANGES TABS AT BOTTOM
Sheets("AOB RSL").Select
Sheets("AOB RSL").Move Before:=Sheets(1)
Sheets("MEDCO RSL").Select
Sheets("MEDCO RSL").Move Before:=Sheets(2)
Sheets("IG MEDCO").Select
Sheets("IG MEDCO").Move Before:=Sheets(3)
Sheets("PAH MEDCO").Select
Sheets("PAH MEDCO").Move Before:=Sheets(4)
Sheets("HOLDS REPORT").Select
Sheets("HOLDS REPORT").Move Before:=Sheets(5)
Sheets("NO SC NOTES").Select
Sheets("NO SC NOTES").Move Before:=Sheets(5)
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("AOB RSL").Select
'STATS TABS DATA SORTING CODE
'REMOVES DOUBLE SHIPS FROM STATS DATA TAB
Sheets("STATS DATA").Select
'REMOVES NO SC ORDERS FROM STATS DATA TAB
Range("A1").Select
Dim CelSDNOSC As Range, RngSDNOSC As Range, iSDNOSC As Long
Set RngSDNOSC = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iSDNOSC = RngSDNOSC.Count To 1 Step -1
If RngSDNOSC(iSDNOSC).Value = "MEDCO MAIL" _
And RngSDNOSC(iSDNOSC).Offset(0, 2).Value = "NO" _
Then RngSDNOSC(iSDNOSC).EntireRow.Delete
Next iSDNOSC
Range("A1").Select
'REMOVES HOLDS FROM STATS DATA TAB
Range("A1").Select
Dim CelSTATSH As Range, RngSTATSH As Range, iSTATSH As Long
Set RngSTATSH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iSTATSH = RngSTATSH.Count To 1 Step -1
If RngSTATSH(iSTATSH).Value = "Y" _
Then RngSTATSH(iSTATSH).EntireRow.Delete
Next iSTATSH
Range("A1").Select
'REMOVES DOUBLE SHIPS FROM STATS DATA TAB
Dim CelSTATSTRCONLY As Range, RngSTATSTRCONLY As Range, iSTATSTRCONLY As Long
Set RngSTATSTRCONLY = Columns("U").SpecialCells(xlConstants, xlTextValues)
For iSTATSTRCONLY = RngSTATSTRCONLY.Count To 2 Step -1
Select Case Range("U" & iSTATSTRCONLY).Value
Case "PAH TRC", "IG TRC"
'do nothing
Case Else
Rows(iSTATSTRCONLY).Delete
End Select
Next iSTATSTRCONLY
'START OF PIVOT TABLE BUILDS
-------THIRD BREAK_____
'STATS PIVOT BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
Set objTable = Sheets("STATS DATA").PivotTableWizard
Set objField = objTable.PivotFields("MEDCO MAIL OR AOB")
Set objField2 = objTable.PivotFields("TRC")
objField.Orientation = xlColumnField
objField2.Orientation = xlColumnField
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
Set objField = objTable.PivotFields("DAY")
objField.PivotItems("FUTURE SHIP").Visible = True
objField.PivotItems("NEXT DAY").Visible = True
objField.PivotItems("PREVIOUS SHIP").Visible = True
objField.Orientation = xlRowField
objField.PivotItems( _
"FUTURE SHIP").Position = 1
objField.PivotItems( _
"NEXT DAY").Position = 1
objField.PivotItems( _
"SAME DAY").Position = 1
objField.PivotItems( _
"PREVIOUS SHIP").Position = 1
objField.PivotItems( _
"SUNDAY SHIP").Position = 1
objField.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objField = objTable.PivotFields("GROUPER")
Dim varItemList() As Variant
Dim strItem1 As String
Dim i As Long
Application.ScreenUpdating = False
varItemList = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "A-P-T")
strItem1 = varItemList(LBound(varItemList))
With objTable.PivotFields("GROUPER")
.PivotItems(strItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i) <> strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i
For i = LBound(varItemList) + 1 To UBound(varItemList)
.PivotItems(varItemList(i)).Visible = True
Next i
End With
objField.Orientation = xlRowField
objField.PivotItems( _
"A-P-T").Position = 1
objField.PivotItems( _
"PAH ORALS").Position = 1
objField.PivotItems( _
"PAH INH").Position = 1
objField.PivotItems( _
"PAH INJ").Position = 1
objField.PivotItems( _
"ALPHA-IG").Position = 1
objField.PivotItems( _
"HAE").Position = 1
objField.PivotItems( _
"ZYMES").Position = 1
objField.PivotItems( _
"REVA SUSP").Position = 1
Set objField = objTable.PivotFields("THERAPY TYPE")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("RX HOME ID #")
objField.Orientation = xlDataField
objField.Function = xlCount
objField.NumberFormat = "Number"
objTable.RowAxisLayout xlOutlineRow
objTable.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "WORKLOAD STATS"
ActiveWorkbook.ShowPivotTableFieldList = False
objTable.ShowTableStyleRowStripes = True
objTable.ShowTableStyleColumnStripes = True
objTable.MergeLabels = False
objTable.PivotFields("GROUPER").ShowDetail = False
Range("A1").Select
'WORKING LIST BUILDS - AOB RSL
'AOB SAME DAY RSL
Sheets("AOB RSL").Select
Range("A1").Select
Dim objTableRSLW As PivotTable, objFieldRSLW As PivotField
ActiveWorkbook.Sheets("AOB RSL").Select
Range("A1").Select
Set objTableRSLW = Sheets("AOB RSL").PivotTableWizard
Set objFieldRSLW = objTableRSLW.PivotFields("TRC")
objFieldRSLW.Orientation = xlColumnField
Set objFieldRSLW = objTableRSLW.PivotFields("DAY")
objFieldRSLW.PivotItems("FUTURE SHIP").Visible = False
objFieldRSLW.PivotItems("NEXT DAY").Visible = False
objFieldRSLW.PivotItems("PREVIOUS SHIP").Visible = True
objFieldRSLW.Orientation = xlRowField
objFieldRSLW.PivotItems( _
"SAME DAY").Position = 1
objFieldRSLW.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldRSLW.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldRSLW.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldRSLW = objTableRSLW.PivotFields("GROUPER")
Dim varItemListRSLW() As Variant
Dim strItem1RSLW As String
Dim iRSLW As Long
Application.ScreenUpdating = False
varItemListRSLW = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "A-P-T")
strItem1RSLW = varItemListRSLW(LBound(varItemListRSLW))
With objTableRSLW.PivotFields("GROUPER")
.PivotItems(strItem1RSLW).Visible = True
For iRSLW = 1 To .PivotItems.Count
If .PivotItems(iRSLW) <> strItem1RSLW And _
.PivotItems(iRSLW).Visible = True Then
.PivotItems(iRSLW).Visible = False
End If
Next iRSLW
For iRSLW = LBound(varItemListRSLW) + 1 To UBound(varItemListRSLW)
.PivotItems(varItemListRSLW(iRSLW)).Visible = True
Next iRSLW
End With
objFieldRSLW.Orientation = xlRowField
objFieldRSLW.PivotItems( _
"A-P-T").Position = 1
objFieldRSLW.PivotItems( _
"PAH ORALS").Position = 1
objFieldRSLW.PivotItems( _
"PAH INH").Position = 1
objFieldRSLW.PivotItems( _
"PAH INJ").Position = 1
objFieldRSLW.PivotItems( _
"ALPHA-IG").Position = 1
objFieldRSLW.PivotItems( _
"HAE").Position = 1
objFieldRSLW.PivotItems( _
"ZYMES").Position = 1
objFieldRSLW.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldRSLW = objTableRSLW.PivotFields("THERAPY TYPE")
objFieldRSLW.Orientation = xlRowField
Set objFieldRSLW = objTableRSLW.PivotFields("RX HOME ID #")
objFieldRSLW.Orientation = xlRowField
Set objFieldRSLW = objTableRSLW.PivotFields("RX HOME ID #")
objFieldRSLW.Orientation = xlDataField
objFieldRSLW.Function = xlCount
objFieldRSLW.NumberFormat = "Number"
objTableRSLW.RowAxisLayout xlOutlineRow
objTableRSLW.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "AOB LIST"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'AOB NEXT DAY RSL BUILD
Range("A1").Select
Dim objTableRSLNDW As PivotTable, objFieldRSLNDW As PivotField
ActiveWorkbook.Sheets("AOB RSL").Select
Range("A1").Select
Set objTableRSLNDW = Sheets("AOB RSL").PivotTableWizard
Set objFieldRSLNDW = objTableRSLNDW.PivotFields("TRC")
objFieldRSLNDW.Orientation = xlColumnField
Set objFieldRSLNDW = objTableRSLNDW.PivotFields("DAY")
objFieldRSLNDW.PivotItems("FUTURE SHIP").Visible = False
objFieldRSLNDW.PivotItems("NEXT DAY").Visible = True
objFieldRSLNDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldRSLNDW.PivotItems("SAME DAY").Visible = False
objFieldRSLNDW.Orientation = xlRowField
objFieldRSLNDW.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldRSLNDW = objTableRSLNDW.PivotFields("THERAPY TYPE")
Dim varItemListRSLNDW() As Variant
Dim strItem1RSLNDW As String
Dim iRSLNDW As Long
Application.ScreenUpdating = False
varItemListRSLNDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1RSLNDW = varItemListRSLNDW(LBound(varItemListRSLNDW))
With objTableRSLNDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1RSLNDW).Visible = True
For iRSLNDW = 1 To .PivotItems.Count
If .PivotItems(iRSLNDW) <> strItem1RSLNDW And _
.PivotItems(iRSLNDW).Visible = True Then
.PivotItems(iRSLNDW).Visible = False
End If
Next iRSLNDW
For iRSLNDW = LBound(varItemListRSLNDW) + 1 To UBound(varItemListRSLNDW)
.PivotItems(varItemListRSLNDW(iRSLNDW)).Visible = True
Next iRSLNDW
End With
objFieldRSLNDW.Orientation = xlRowField
objFieldRSLNDW.Orientation = xlRowField
objFieldRSLNDW.PivotItems( _
"SILD").Position = 1
objFieldRSLNDW.PivotItems( _
"REVA").Position = 1
objFieldRSLNDW.PivotItems( _
"ADCR").Position = 1
objFieldRSLNDW.PivotItems( _
"LETA").Position = 1
objFieldRSLNDW.PivotItems( _
"TPAP").Position = 1
objFieldRSLNDW.PivotItems( _
"TRAC").Position = 1
objFieldRSLNDW.PivotItems( _
"TRAB").Position = 1
objFieldRSLNDW.PivotItems( _
"REVC").Position = 1
objFieldRSLNDW.PivotItems( _
"TOBI").Position = 1
objFieldRSLNDW.PivotItems( _
"PUL").Position = 1
objFieldRSLNDW.PivotItems( _
"ACTP").Position = 1
objFieldRSLNDW.PivotItems( _
"GMPX").Position = 1
objFieldRSLNDW.PivotItems( _
"FLBO").Position = 1
objFieldRSLNDW.PivotItems( _
"IVGF").Position = 1
objFieldRSLNDW.PivotItems( _
"GKSC").Position = 1
objFieldRSLNDW.PivotItems( _
"GKIV").Position = 1
objFieldRSLNDW.PivotItems( _
"GGSC").Position = 1
objFieldRSLNDW.PivotItems( _
"GGIV").Position = 1
objFieldRSLNDW.PivotItems( _
"IVGS").Position = 1
objFieldRSLNDW.PivotItems( _
"GAMS").Position = 1
objFieldRSLNDW.PivotItems( _
"HIZA").Position = 1
objFieldRSLNDW.PivotItems( _
"IVGP").Position = 1
objFieldRSLNDW.PivotItems( _
"GAMC").Position = 1
objFieldRSLNDW.PivotItems( _
"GLSA").Position = 1
objFieldRSLNDW.PivotItems( _
"ZEMA").Position = 1
objFieldRSLNDW.PivotItems( _
"ARAL").Position = 1
objFieldRSLNDW.PivotItems( _
"REMP").Position = 1
objFieldRSLNDW.PivotItems( _
"REMO").Position = 1
objFieldRSLNDW.PivotItems( _
"RMIV").Position = 1
objFieldRSLNDW.PivotItems( _
"RMIP").Position = 1
objFieldRSLNDW.PivotItems( _
"TYVS").Position = 1
objFieldRSLNDW.PivotItems( _
"TYVP").Position = 1
objFieldRSLNDW.PivotItems( _
"TYVA").Position = 1
objFieldRSLNDW.PivotItems( _
"VENT").Position = 1
objFieldRSLNDW.PivotItems( _
"VENP").Position = 1
objFieldRSLNDW.PivotItems( _
"FPAP").Position = 1
objFieldRSLNDW.PivotItems( _
"EPOP").Position = 1
objFieldRSLNDW.PivotItems( _
"EPAP").Position = 1
objFieldRSLNDW.PivotItems( _
"EPAC").Position = 1
objFieldRSLNDW.PivotItems( _
"F100").Position = 1
objFieldRSLNDW.PivotItems( _
"FL50").Position = 1
objFieldRSLNDW.PivotItems( _
"FZYR").Position = 1
objFieldRSLNDW.PivotItems( _
"BERT").Position = 1
objFieldRSLNDW.PivotItems( _
"ELAP").Position = 1
objFieldRSLNDW.PivotItems( _
"ALDZ").Position = 1
objFieldRSLNDW.PivotItems( _
"FABZ").Position = 1
objFieldRSLNDW.PivotItems( _
"FABF").Position = 1
objFieldRSLNDW.PivotItems( _
"NGLZ").Position = 1
objFieldRSLNDW.PivotItems( _
"NGLF").Position = 1
objFieldRSLNDW.PivotItems( _
"MYOZ").Position = 1
objFieldRSLNDW.PivotItems( _
"LUMZ").Position = 1
objFieldRSLNDW.PivotItems( _
"VPRV").Position = 1
objFieldRSLNDW.PivotItems( _
"ZYMF").Position = 1
objFieldRSLNDW.PivotItems( _
"ZYME").Position = 1
Set objFieldRSLNDW = objTableRSLNDW.PivotFields("RX HOME ID #")
objFieldRSLNDW.Orientation = xlRowField
Set objFieldRSLNDW = objTableRSLNDW.PivotFields("RX HOME ID #")
objFieldRSLNDW.Orientation = xlDataField
objFieldRSLNDW.Function = xlCount
objFieldRSLNDW.NumberFormat = "Number"
objTableRSLNDW.RowAxisLayout xlOutlineRow
objTableRSLNDW.TableStyle2 = "PivotStyleMedium10"
objTableRSLNDW.Location = "'AOB LIST'!$J$1"
Sheets("Sheet11").Select
Sheets("Sheet11").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB LIST").Select
Columns("H:H").Select
Selection.ColumnWidth = 1.1
Columns("I:I").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("AOB LIST").Select
Range("A1").Select
'AOB FUTURE RSL BUILD
Range("A1").Select
Dim objTableRSLFDW As PivotTable, objFieldRSLFDW As PivotField
ActiveWorkbook.Sheets("AOB RSL").Select
Range("A1").Select
Set objTableRSLFDW = Sheets("AOB RSL").PivotTableWizard
Set objFieldRSLFDW = objTableRSLFDW.PivotFields("TRC")
objFieldRSLFDW.Orientation = xlColumnField
Set objFieldRSLFDW = objTableRSLFDW.PivotFields("DAY")
objFieldRSLFDW.PivotItems("FUTURE SHIP").Visible = True
objFieldRSLFDW.PivotItems("NEXT DAY").Visible = False
objFieldRSLFDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldRSLFDW.PivotItems("SAME DAY").Visible = False
objFieldRSLFDW.Orientation = xlRowField
objFieldRSLFDW.PivotItems( _
"FUTURE SHIP").Position = 1
Set objFieldRSLFDW = objTableRSLFDW.PivotFields("THERAPY TYPE")
Dim varItemListRSLFDW() As Variant
Dim strItem1RSLFDW As String
Dim iRSLFDW As Long
Application.ScreenUpdating = False
varItemListRSLFDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1RSLFDW = varItemListRSLFDW(LBound(varItemListRSLFDW))
With objTableRSLFDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1RSLFDW).Visible = True
For iRSLFDW = 1 To .PivotItems.Count
If .PivotItems(iRSLFDW) <> strItem1RSLFDW And _
.PivotItems(iRSLFDW).Visible = True Then
.PivotItems(iRSLFDW).Visible = False
End If
Next iRSLFDW
For iRSLFDW = LBound(varItemListRSLFDW) + 1 To UBound(varItemListRSLFDW)
.PivotItems(varItemListRSLFDW(iRSLFDW)).Visible = True
Next iRSLFDW
End With
objFieldRSLFDW.Orientation = xlRowField
objFieldRSLFDW.Orientation = xlRowField
objFieldRSLFDW.PivotItems( _
"SILD").Position = 1
objFieldRSLFDW.PivotItems( _
"REVA").Position = 1
objFieldRSLFDW.PivotItems( _
"ADCR").Position = 1
objFieldRSLFDW.PivotItems( _
"LETA").Position = 1
objFieldRSLFDW.PivotItems( _
"TPAP").Position = 1
objFieldRSLFDW.PivotItems( _
"TRAC").Position = 1
objFieldRSLFDW.PivotItems( _
"TRAB").Position = 1
objFieldRSLFDW.PivotItems( _
"REVC").Position = 1
objFieldRSLFDW.PivotItems( _
"TOBI").Position = 1
objFieldRSLFDW.PivotItems( _
"PUL").Position = 1
objFieldRSLFDW.PivotItems( _
"ACTP").Position = 1
objFieldRSLFDW.PivotItems( _
"GMPX").Position = 1
objFieldRSLFDW.PivotItems( _
"FLBO").Position = 1
objFieldRSLFDW.PivotItems( _
"IVGF").Position = 1
objFieldRSLFDW.PivotItems( _
"GKSC").Position = 1
objFieldRSLFDW.PivotItems( _
"GKIV").Position = 1
objFieldRSLFDW.PivotItems( _
"GGSC").Position = 1
objFieldRSLFDW.PivotItems( _
"GGIV").Position = 1
objFieldRSLFDW.PivotItems( _
"IVGS").Position = 1
objFieldRSLFDW.PivotItems( _
"GAMS").Position = 1
objFieldRSLFDW.PivotItems( _
"HIZA").Position = 1
objFieldRSLFDW.PivotItems( _
"IVGP").Position = 1
objFieldRSLFDW.PivotItems( _
"GAMC").Position = 1
objFieldRSLFDW.PivotItems( _
"GLSA").Position = 1
objFieldRSLFDW.PivotItems( _
"ZEMA").Position = 1
objFieldRSLFDW.PivotItems( _
"ARAL").Position = 1
objFieldRSLFDW.PivotItems( _
"REMP").Position = 1
objFieldRSLFDW.PivotItems( _
"REMO").Position = 1
objFieldRSLFDW.PivotItems( _
"RMIV").Position = 1
objFieldRSLFDW.PivotItems( _
"RMIP").Position = 1
objFieldRSLFDW.PivotItems( _
"TYVS").Position = 1
objFieldRSLFDW.PivotItems( _
"TYVP").Position = 1
objFieldRSLFDW.PivotItems( _
"TYVA").Position = 1
objFieldRSLFDW.PivotItems( _
"VENT").Position = 1
objFieldRSLFDW.PivotItems( _
"VENP").Position = 1
objFieldRSLFDW.PivotItems( _
"FPAP").Position = 1
objFieldRSLFDW.PivotItems( _
"EPOP").Position = 1
objFieldRSLFDW.PivotItems( _
"EPAP").Position = 1
objFieldRSLFDW.PivotItems( _
"EPAC").Position = 1
objFieldRSLFDW.PivotItems( _
"F100").Position = 1
objFieldRSLFDW.PivotItems( _
"FL50").Position = 1
objFieldRSLFDW.PivotItems( _
"FZYR").Position = 1
objFieldRSLFDW.PivotItems( _
"BERT").Position = 1
objFieldRSLFDW.PivotItems( _
"ELAP").Position = 1
objFieldRSLFDW.PivotItems( _
"ALDZ").Position = 1
objFieldRSLFDW.PivotItems( _
"FABZ").Position = 1
objFieldRSLFDW.PivotItems( _
"FABF").Position = 1
objFieldRSLFDW.PivotItems( _
"NGLZ").Position = 1
objFieldRSLFDW.PivotItems( _
"NGLF").Position = 1
objFieldRSLFDW.PivotItems( _
"MYOZ").Position = 1
objFieldRSLFDW.PivotItems( _
"LUMZ").Position = 1
objFieldRSLFDW.PivotItems( _
"VPRV").Position = 1
objFieldRSLFDW.PivotItems( _
"ZYMF").Position = 1
objFieldRSLFDW.PivotItems( _
"ZYME").Position = 1
Set objFieldRSLFDW = objTableRSLFDW.PivotFields("RX HOME ID #")
objFieldRSLFDW.Orientation = xlRowField
Set objFieldRSLFDW = objTableRSLFDW.PivotFields("RX HOME ID #")
objFieldRSLFDW.Orientation = xlDataField
objFieldRSLFDW.Function = xlCount
objFieldRSLFDW.NumberFormat = "Number"
objTableRSLFDW.RowAxisLayout xlOutlineRow
objTableRSLFDW.TableStyle2 = "PivotStyleMedium11"
objTableRSLFDW.Location = "'AOB LIST'!$R$1"
Sheets("Sheet12").Select
Sheets("Sheet12").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB LIST").Select
Columns("P:P").Select
Selection.ColumnWidth = 1.1
Columns("Q:Q").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("AOB LIST").Select
Range("A1").Select
_____FOURTH BREAK_______
'WORKING LIST BUILDS - MEDCO RSL
'MEDCO SAME DAY RSL
Sheets("MEDCO RSL").Select
Range("A1").Select
Dim objTableMEDCORSLW As PivotTable, objFieldMEDCORSLW As PivotField
ActiveWorkbook.Sheets("MEDCO RSL").Select
Range("A1").Select
Set objTableMEDCORSLW = Sheets("MEDCO RSL").PivotTableWizard
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("TRC")
objFieldMEDCORSLW.Orientation = xlColumnField
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("DAY")
objFieldMEDCORSLW.PivotItems("FUTURE SHIP").Visible = False
objFieldMEDCORSLW.PivotItems("NEXT DAY").Visible = False
objFieldMEDCORSLW.PivotItems("PREVIOUS SHIP").Visible = True
objFieldMEDCORSLW.Orientation = xlRowField
objFieldMEDCORSLW.PivotItems( _
"SAME DAY").Position = 1
objFieldMEDCORSLW.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldMEDCORSLW.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldMEDCORSLW.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("GROUPER")
Dim varItemListMEDCORSLW() As Variant
Dim strItem1MEDCORSLW As String
Dim iMEDCORSLW As Long
Application.ScreenUpdating = False
varItemListMEDCORSLW = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "A-P-T")
strItem1MEDCORSLW = varItemListMEDCORSLW(LBound(varItemListMEDCORSLW))
With objTableMEDCORSLW.PivotFields("GROUPER")
.PivotItems(strItem1MEDCORSLW).Visible = True
For iMEDCORSLW = 1 To .PivotItems.Count
If .PivotItems(iMEDCORSLW) <> strItem1MEDCORSLW And _
.PivotItems(iMEDCORSLW).Visible = True Then
.PivotItems(iMEDCORSLW).Visible = False
End If
Next iMEDCORSLW
For iMEDCORSLW = LBound(varItemListMEDCORSLW) + 1 To UBound(varItemListMEDCORSLW)
.PivotItems(varItemListMEDCORSLW(iMEDCORSLW)).Visible = True
Next iMEDCORSLW
End With
objFieldMEDCORSLW.Orientation = xlRowField
objFieldMEDCORSLW.PivotItems( _
"A-P-T").Position = 1
objFieldMEDCORSLW.PivotItems( _
"PAH ORALS").Position = 1
objFieldMEDCORSLW.PivotItems( _
"PAH INH").Position = 1
objFieldMEDCORSLW.PivotItems( _
"PAH INJ").Position = 1
objFieldMEDCORSLW.PivotItems( _
"ALPHA-IG").Position = 1
objFieldMEDCORSLW.PivotItems( _
"HAE").Position = 1
objFieldMEDCORSLW.PivotItems( _
"ZYMES").Position = 1
objFieldMEDCORSLW.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("THERAPY TYPE")
objFieldMEDCORSLW.Orientation = xlRowField
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("RX HOME ID #")
objFieldMEDCORSLW.Orientation = xlRowField
Set objFieldMEDCORSLW = objTableMEDCORSLW.PivotFields("RX HOME ID #")
objFieldMEDCORSLW.Orientation = xlDataField
objFieldMEDCORSLW.Function = xlCount
objFieldMEDCORSLW.NumberFormat = "Number"
objTableMEDCORSLW.RowAxisLayout xlOutlineRow
objTableMEDCORSLW.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet13").Select
Sheets("Sheet13").Name = "MEDCO LIST"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'MEDCO NEXT DAY RSL BUILD
Range("A1").Select
Dim objTableMEDCORSLNDW As PivotTable, objFieldMEDCORSLNDW As PivotField
ActiveWorkbook.Sheets("MEDCO RSL").Select
Range("A1").Select
Set objTableMEDCORSLNDW = Sheets("MEDCO RSL").PivotTableWizard
Set objFieldMEDCORSLNDW = objTableMEDCORSLNDW.PivotFields("TRC")
objFieldMEDCORSLNDW.Orientation = xlColumnField
Set objFieldMEDCORSLNDW = objTableMEDCORSLNDW.PivotFields("DAY")
objFieldMEDCORSLNDW.PivotItems("FUTURE SHIP").Visible = False
objFieldMEDCORSLNDW.PivotItems("NEXT DAY").Visible = True
objFieldMEDCORSLNDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldMEDCORSLNDW.PivotItems("SAME DAY").Visible = False
objFieldMEDCORSLNDW.Orientation = xlRowField
objFieldMEDCORSLNDW.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldMEDCORSLNDW = objTableMEDCORSLNDW.PivotFields("THERAPY TYPE")
Dim varItemListMEDCORSLNDW() As Variant
Dim strItem1MEDCORSLNDW As String
Dim iMEDCORSLNDW As Long
Application.ScreenUpdating = False
varItemListMEDCORSLNDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1MEDCORSLNDW = varItemListMEDCORSLNDW(LBound(varItemListMEDCORSLNDW))
With objTableMEDCORSLNDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1MEDCORSLNDW).Visible = True
For iMEDCORSLNDW = 1 To .PivotItems.Count
If .PivotItems(iMEDCORSLNDW) <> strItem1MEDCORSLNDW And _
.PivotItems(iMEDCORSLNDW).Visible = True Then
.PivotItems(iMEDCORSLNDW).Visible = False
End If
Next iMEDCORSLNDW
For iMEDCORSLNDW = LBound(varItemListMEDCORSLNDW) + 1 To UBound(varItemListMEDCORSLNDW)
.PivotItems(varItemListMEDCORSLNDW(iMEDCORSLNDW)).Visible = True
Next iMEDCORSLNDW
End With
objFieldMEDCORSLNDW.Orientation = xlRowField
objFieldMEDCORSLNDW.Orientation = xlRowField
objFieldMEDCORSLNDW.PivotItems( _
"SILD").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"REVA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ADCR").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"LETA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TPAP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TRAC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TRAB").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"REVC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TOBI").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"PUL").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ACTP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GMPX").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FLBO").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"IVGF").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GKSC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GKIV").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GGSC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GGIV").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"IVGS").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GAMS").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"HIZA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"IVGP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GAMC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"GLSA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ZEMA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ARAL").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"REMP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"REMO").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"RMIV").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"RMIP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TYVS").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TYVP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"TYVA").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"VENT").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"VENP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FPAP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"EPOP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"EPAP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"EPAC").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"F100").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FL50").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FZYR").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"BERT").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ELAP").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ALDZ").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FABZ").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"FABF").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"NGLZ").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"NGLF").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"MYOZ").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"LUMZ").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"VPRV").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ZYMF").Position = 1
objFieldMEDCORSLNDW.PivotItems( _
"ZYME").Position = 1
Set objFieldMEDCORSLNDW = objTableMEDCORSLNDW.PivotFields("RX HOME ID #")
objFieldMEDCORSLNDW.Orientation = xlRowField
Set objFieldMEDCORSLNDW = objTableMEDCORSLNDW.PivotFields("RX HOME ID #")
objFieldMEDCORSLNDW.Orientation = xlDataField
objFieldMEDCORSLNDW.Function = xlCount
objFieldMEDCORSLNDW.NumberFormat = "Number"
objTableMEDCORSLNDW.RowAxisLayout xlOutlineRow
objTableMEDCORSLNDW.TableStyle2 = "PivotStyleMedium10"
objTableMEDCORSLNDW.Location = "'MEDCO LIST'!$J$1"
Sheets("Sheet14").Select
Sheets("Sheet14").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("MEDCO LIST").Select
Columns("H:H").Select
Selection.ColumnWidth = 1.1
Columns("I:I").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("MEDCO LIST").Select
Range("A1").Select
'MEDCO FUTURE RSL BUILD
Range("A1").Select
Dim objTableMEDCORSLFDW As PivotTable, objFieldMEDCORSLFDW As PivotField
ActiveWorkbook.Sheets("MEDCO RSL").Select
Range("A1").Select
Set objTableMEDCORSLFDW = Sheets("MEDCO RSL").PivotTableWizard
Set objFieldMEDCORSLFDW = objTableMEDCORSLFDW.PivotFields("TRC")
objFieldMEDCORSLFDW.Orientation = xlColumnField
Set objFieldMEDCORSLFDW = objTableMEDCORSLFDW.PivotFields("DAY")
objFieldMEDCORSLFDW.PivotItems("FUTURE SHIP").Visible = True
objFieldMEDCORSLFDW.PivotItems("NEXT DAY").Visible = False
objFieldMEDCORSLFDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldMEDCORSLFDW.PivotItems("SAME DAY").Visible = False
objFieldMEDCORSLFDW.Orientation = xlRowField
objFieldMEDCORSLFDW.PivotItems( _
"FUTURE SHIP").Position = 1
Set objFieldMEDCORSLFDW = objTableMEDCORSLFDW.PivotFields("THERAPY TYPE")
Dim varItemListMEDCORSLFDW() As Variant
Dim strItem1MEDCORSLFDW As String
Dim iMEDCORSLFDW As Long
Application.ScreenUpdating = False
varItemListMEDCORSLFDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1MEDCORSLFDW = varItemListMEDCORSLFDW(LBound(varItemListMEDCORSLFDW))
With objTableMEDCORSLFDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1MEDCORSLFDW).Visible = True
For iMEDCORSLFDW = 1 To .PivotItems.Count
If .PivotItems(iMEDCORSLFDW) <> strItem1MEDCORSLFDW And _
.PivotItems(iMEDCORSLFDW).Visible = True Then
.PivotItems(iMEDCORSLFDW).Visible = False
End If
Next iMEDCORSLFDW
For iMEDCORSLFDW = LBound(varItemListMEDCORSLFDW) + 1 To UBound(varItemListMEDCORSLFDW)
.PivotItems(varItemListMEDCORSLFDW(iMEDCORSLFDW)).Visible = True
Next iMEDCORSLFDW
End With
objFieldMEDCORSLFDW.Orientation = xlRowField
objFieldMEDCORSLFDW.Orientation = xlRowField
objFieldMEDCORSLFDW.PivotItems( _
"SILD").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"REVA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ADCR").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"LETA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TPAP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TRAC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TRAB").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"REVC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TOBI").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"PUL").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ACTP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GMPX").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FLBO").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"IVGF").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GKSC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GKIV").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GGSC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GGIV").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"IVGS").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GAMS").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"HIZA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"IVGP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GAMC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"GLSA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ZEMA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ARAL").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"REMP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"REMO").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"RMIV").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"RMIP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TYVS").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TYVP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"TYVA").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"VENT").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"VENP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FPAP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"EPOP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"EPAP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"EPAC").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"F100").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FL50").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FZYR").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"BERT").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ELAP").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ALDZ").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FABZ").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"FABF").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"NGLZ").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"NGLF").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"MYOZ").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"LUMZ").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"VPRV").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ZYMF").Position = 1
objFieldMEDCORSLFDW.PivotItems( _
"ZYME").Position = 1
Set objFieldMEDCORSLFDW = objTableMEDCORSLFDW.PivotFields("RX HOME ID #")
objFieldMEDCORSLFDW.Orientation = xlRowField
Set objFieldMEDCORSLFDW = objTableMEDCORSLFDW.PivotFields("RX HOME ID #")
objFieldMEDCORSLFDW.Orientation = xlDataField
objFieldMEDCORSLFDW.Function = xlCount
objFieldMEDCORSLFDW.NumberFormat = "Number"
objTableMEDCORSLFDW.RowAxisLayout xlOutlineRow
objTableMEDCORSLFDW.TableStyle2 = "PivotStyleMedium11"
objTableMEDCORSLFDW.Location = "'MEDCO LIST'!$R$1"
Sheets("Sheet15").Select
Sheets("Sheet15").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("MEDCO LIST").Select
Columns("P:P").Select
Selection.ColumnWidth = 1.1
Columns("Q:Q").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("MEDCO LIST").Select
Range("A1").Select
_____FIFTH BREAK_________
‘'WORKING LIST BUILDS - IG MEDCO
'IG MEDCO (ZERO/VARIABLE FILL) LIST BUILD
Sheets("IG MEDCO").Select
Range("A1").Select
Dim objTableIGMEDCORSLW As PivotTable, objFieldIGMEDCORSLW As PivotField
ActiveWorkbook.Sheets("IG MEDCO").Select
Range("A1").Select
Set objTableIGMEDCORSLW = Sheets("IG MEDCO").PivotTableWizard
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("TRC")
objFieldIGMEDCORSLW.Orientation = xlColumnField
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("DAY")
objFieldIGMEDCORSLW.PivotItems("FUTURE SHIP").Visible = False
objFieldIGMEDCORSLW.PivotItems("NEXT DAY").Visible = False
objFieldIGMEDCORSLW.PivotItems("PREVIOUS SHIP").Visible = True
objFieldIGMEDCORSLW.Orientation = xlRowField
objFieldIGMEDCORSLW.PivotItems( _
"SAME DAY").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("GROUPER")
Dim varItemListIGMEDCORSLW() As Variant
Dim strItem1IGMEDCORSLW As String
Dim iIGMEDCORSLW As Long
Application.ScreenUpdating = False
varItemListIGMEDCORSLW = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "A-P-T")
strItem1IGMEDCORSLW = varItemListIGMEDCORSLW(LBound(varItemListIGMEDCORSLW))
With objTableIGMEDCORSLW.PivotFields("GROUPER")
.PivotItems(strItem1IGMEDCORSLW).Visible = True
For iIGMEDCORSLW = 1 To .PivotItems.Count
If .PivotItems(iIGMEDCORSLW) <> strItem1IGMEDCORSLW And _
.PivotItems(iIGMEDCORSLW).Visible = True Then
.PivotItems(iIGMEDCORSLW).Visible = False
End If
Next iIGMEDCORSLW
For iIGMEDCORSLW = LBound(varItemListIGMEDCORSLW) + 1 To UBound(varItemListIGMEDCORSLW)
.PivotItems(varItemListIGMEDCORSLW(iIGMEDCORSLW)).Visible = True
Next iIGMEDCORSLW
End With
objFieldIGMEDCORSLW.Orientation = xlRowField
objFieldIGMEDCORSLW.PivotItems( _
"A-P-T").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"PAH ORALS").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"PAH INH").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"PAH INJ").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"ALPHA-IG").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"HAE").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"ZYMES").Position = 1
objFieldIGMEDCORSLW.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("THERAPY TYPE")
objFieldIGMEDCORSLW.Orientation = xlRowField
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLW.Orientation = xlRowField
Set objFieldIGMEDCORSLW = objTableIGMEDCORSLW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLW.Orientation = xlDataField
objFieldIGMEDCORSLW.Function = xlCount
objFieldIGMEDCORSLW.NumberFormat = "Number"
objTableIGMEDCORSLW.RowAxisLayout xlOutlineRow
objTableIGMEDCORSLW.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet16").Select
Sheets("Sheet16").Name = "IG (ZERO/VARIABLE FILL) LIST"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'IG MEDCO NEXT DAY (ZERO/VARIABLE FILL) LIST BUILD
Range("A1").Select
Dim objTableIGMEDCORSLNDW As PivotTable, objFieldIGMEDCORSLNDW As PivotField
ActiveWorkbook.Sheets("IG MEDCO").Select
Range("A1").Select
Set objTableIGMEDCORSLNDW = Sheets("IG MEDCO").PivotTableWizard
Set objFieldIGMEDCORSLNDW = objTableIGMEDCORSLNDW.PivotFields("TRC")
objFieldIGMEDCORSLNDW.Orientation = xlColumnField
Set objFieldIGMEDCORSLNDW = objTableIGMEDCORSLNDW.PivotFields("DAY")
objFieldIGMEDCORSLNDW.PivotItems("FUTURE SHIP").Visible = False
objFieldIGMEDCORSLNDW.PivotItems("NEXT DAY").Visible = True
objFieldIGMEDCORSLNDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldIGMEDCORSLNDW.PivotItems("SAME DAY").Visible = False
objFieldIGMEDCORSLNDW.Orientation = xlRowField
objFieldIGMEDCORSLNDW.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldIGMEDCORSLNDW = objTableIGMEDCORSLNDW.PivotFields("THERAPY TYPE")
Dim varItemListIGMEDCORSLNDW() As Variant
Dim strItem1IGMEDCORSLNDW As String
Dim iIGMEDCORSLNDW As Long
Application.ScreenUpdating = False
varItemListIGMEDCORSLNDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1IGMEDCORSLNDW = varItemListIGMEDCORSLNDW(LBound(varItemListIGMEDCORSLNDW))
With objTableIGMEDCORSLNDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1IGMEDCORSLNDW).Visible = True
For iIGMEDCORSLNDW = 1 To .PivotItems.Count
If .PivotItems(iIGMEDCORSLNDW) <> strItem1IGMEDCORSLNDW And _
.PivotItems(iIGMEDCORSLNDW).Visible = True Then
.PivotItems(iIGMEDCORSLNDW).Visible = False
End If
Next iIGMEDCORSLNDW
For iIGMEDCORSLNDW = LBound(varItemListIGMEDCORSLNDW) + 1 To UBound(varItemListIGMEDCORSLNDW)
.PivotItems(varItemListIGMEDCORSLNDW(iIGMEDCORSLNDW)).Visible = True
Next iIGMEDCORSLNDW
End With
objFieldIGMEDCORSLNDW.Orientation = xlRowField
objFieldIGMEDCORSLNDW.Orientation = xlRowField
objFieldIGMEDCORSLNDW.PivotItems( _
"SILD").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"REVA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ADCR").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"LETA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TPAP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TRAC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TRAB").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"REVC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TOBI").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"PUL").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ACTP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GMPX").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FLBO").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"IVGF").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GKSC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GKIV").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GGSC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GGIV").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"IVGS").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GAMS").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"HIZA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"IVGP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GAMC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"GLSA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ZEMA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ARAL").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"REMP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"REMO").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"RMIV").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"RMIP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TYVS").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TYVP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"TYVA").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"VENT").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"VENP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FPAP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"EPOP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"EPAP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"EPAC").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"F100").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FL50").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FZYR").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"BERT").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ELAP").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ALDZ").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FABZ").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"FABF").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"NGLZ").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"NGLF").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"MYOZ").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"LUMZ").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"VPRV").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ZYMF").Position = 1
objFieldIGMEDCORSLNDW.PivotItems( _
"ZYME").Position = 1
Set objFieldIGMEDCORSLNDW = objTableIGMEDCORSLNDW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLNDW.Orientation = xlRowField
Set objFieldIGMEDCORSLNDW = objTableIGMEDCORSLNDW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLNDW.Orientation = xlDataField
objFieldIGMEDCORSLNDW.Function = xlCount
objFieldIGMEDCORSLNDW.NumberFormat = "Number"
objTableIGMEDCORSLNDW.RowAxisLayout xlOutlineRow
objTableIGMEDCORSLNDW.TableStyle2 = "PivotStyleMedium10"
objTableIGMEDCORSLNDW.Location = "'IG (ZERO/VARIABLE FILL) LIST'!$J$1"
Sheets("Sheet17").Select
Sheets("Sheet17").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("IG (ZERO/VARIABLE FILL) LIST").Select
Columns("H:H").Select
Selection.ColumnWidth = 1.1
Columns("I:I").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("IG (ZERO/VARIABLE FILL) LIST").Select
Range("A1").Select
'IG MEDCO FUTURE (ZERO/VARIABLE FILL) LIST BUILD
Range("A1").Select
Dim objTableIGMEDCORSLFDW As PivotTable, objFieldIGMEDCORSLFDW As PivotField
ActiveWorkbook.Sheets("IG MEDCO").Select
Range("A1").Select
Set objTableIGMEDCORSLFDW = Sheets("IG MEDCO").PivotTableWizard
Set objFieldIGMEDCORSLFDW = objTableIGMEDCORSLFDW.PivotFields("TRC")
objFieldIGMEDCORSLFDW.Orientation = xlColumnField
Set objFieldIGMEDCORSLFDW = objTableIGMEDCORSLFDW.PivotFields("DAY")
objFieldIGMEDCORSLFDW.PivotItems("FUTURE SHIP").Visible = True
objFieldIGMEDCORSLFDW.PivotItems("NEXT DAY").Visible = False
objFieldIGMEDCORSLFDW.PivotItems("PREVIOUS SHIP").Visible = False
objFieldIGMEDCORSLFDW.PivotItems("SAME DAY").Visible = False
objFieldIGMEDCORSLFDW.Orientation = xlRowField
objFieldIGMEDCORSLFDW.PivotItems( _
"FUTURE SHIP").Position = 1
Set objFieldIGMEDCORSLFDW = objTableIGMEDCORSLFDW.PivotFields("THERAPY TYPE")
Dim varItemListIGMEDCORSLFDW() As Variant
Dim strItem1IGMEDCORSLFDW As String
Dim iIGMEDCORSLFDW As Long
Application.ScreenUpdating = False
varItemListIGMEDCORSLFDW = Array("SILD", "REVA", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")
strItem1IGMEDCORSLFDW = varItemListIGMEDCORSLFDW(LBound(varItemListIGMEDCORSLFDW))
With objTableIGMEDCORSLFDW.PivotFields("THERAPY TYPE")
.PivotItems(strItem1IGMEDCORSLFDW).Visible = True
For iIGMEDCORSLFDW = 1 To .PivotItems.Count
If .PivotItems(iIGMEDCORSLFDW) <> strItem1IGMEDCORSLFDW And _
.PivotItems(iIGMEDCORSLFDW).Visible = True Then
.PivotItems(iIGMEDCORSLFDW).Visible = False
End If
Next iIGMEDCORSLFDW
For iIGMEDCORSLFDW = LBound(varItemListIGMEDCORSLFDW) + 1 To UBound(varItemListIGMEDCORSLFDW)
.PivotItems(varItemListIGMEDCORSLFDW(iIGMEDCORSLFDW)).Visible = True
Next iIGMEDCORSLFDW
End With
objFieldIGMEDCORSLFDW.Orientation = xlRowField
objFieldIGMEDCORSLFDW.Orientation = xlRowField
objFieldIGMEDCORSLFDW.PivotItems( _
"SILD").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"REVA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ADCR").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"LETA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TPAP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TRAC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TRAB").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"REVC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TOBI").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"PUL").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ACTP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GMPX").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FLBO").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"IVGF").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GKSC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GKIV").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GGSC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GGIV").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"IVGS").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GAMS").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"HIZA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"IVGP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GAMC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"GLSA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ZEMA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ARAL").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"REMP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"REMO").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"RMIV").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"RMIP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TYVS").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TYVP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"TYVA").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"VENT").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"VENP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FPAP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"EPOP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"EPAP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"EPAC").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"F100").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FL50").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FZYR").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"BERT").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ELAP").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ALDZ").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FABZ").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"FABF").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"NGLZ").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"NGLF").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"MYOZ").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"LUMZ").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"VPRV").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ZYMF").Position = 1
objFieldIGMEDCORSLFDW.PivotItems( _
"ZYME").Position = 1
Set objFieldIGMEDCORSLFDW = objTableIGMEDCORSLFDW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLFDW.Orientation = xlRowField
Set objFieldIGMEDCORSLFDW = objTableIGMEDCORSLFDW.PivotFields("RX HOME ID #")
objFieldIGMEDCORSLFDW.Orientation = xlDataField
objFieldIGMEDCORSLFDW.Function = xlCount
objFieldIGMEDCORSLFDW.NumberFormat = "Number"
objTableIGMEDCORSLFDW.RowAxisLayout xlOutlineRow
objTableIGMEDCORSLFDW.TableStyle2 = "PivotStyleMedium11"
objTableIGMEDCORSLFDW.Location = "'IG (ZERO/VARIABLE FILL) LIST'!$R$1"
Sheets("Sheet18").Select
Sheets("Sheet18").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("IG (ZERO/VARIABLE FILL) LIST").Select
Columns("P:P").Select
Selection.ColumnWidth = 1.1
Columns("Q:Q").Select
Selection.ColumnWidth = 1.1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("A1").Select
Range("A1").Select
Sheets("IG (ZERO/VARIABLE FILL) LIST").Select
____TURN UPDATING BACK ON________
Range("A1").Select
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
