rkol297
Board Regular
- Joined
- Nov 12, 2010
- Messages
- 131
- Office Version
- 365
- 2019
- Platform
- Windows
When I split this macro up it does not build the pivot tables as it would if it wasnt split. I have tried split this up at where it starts to build the pivot tables but if I split it up I get a Run Time 1004 Error:Unable to get the PivotTable property of the PivotField Class
How do I split this up so that I can get this to run as two separate macros?
How do I split this up so that I can get this to run as two separate macros?
Code:
Sub RSLDASHBOARDADVANCED_MAIN()
'START SHEET SETUP
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.EnableEvents = False
End With
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("U1").Select
ActiveSheet.Paste
Columns("U:U").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:="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:="REVG", 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:="REGC", 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:="ZAVE", Replacement:="ZYMES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ZAVC", 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:="CNYZ", 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:="KLBT", 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:="GMPX", 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
Columns("U:U").Select
Application.CutCopyMode = False
Selection.Copy
Range("V1").Select
ActiveSheet.Paste
Columns("V:V").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:="ACTI", Replacement:="IG TRC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("U1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "GROUPER"
Range("V1").Select
ActiveCell.FormulaR1C1 = "TRC"
Columns("U:V").Select
Selection.Copy
Range("W1").Select
ActiveSheet.Paste
Range("W1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "HRS TO LOCK"
Columns("W:W").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:="ACTI", Replacement:=".033", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:W1").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("U:U").Select
Selection.Columns.AutoFit
Columns("V:V").Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 9
Columns("W:W").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
Columns("X:X").Select
Selection.Columns.AutoFit
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("X1").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, "X").Value = "SATURDAY SHIP"
ElseIf Weekday(Cells(X, "A").Value) = 1 Then
Cells(X, "X").Value = "SUNDAY SHIP"
Else
Select Case Cells(X, "A").Value - Date
Case Is < 0
Cells(X, "X").Value = "PREVIOUS SHIP"
Case 0
Cells(X, "X").Value = "SAME DAY"
Case 1 - 2 * (Weekday(Date) = 6)
Cells(X, "X").Value = "NEXT DAY"
Case Else
Cells(X, "X").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:X1").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:X1").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:X1").Select
Selection.AutoFilter
Range("A1").Select
ActiveWindow.ScrollColumn = 10
Columns("Y:Y").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 = "DOUBLE SHIPS"
Sheets(1).Select
ActiveSheet.Name = "HOLDS"
ActiveSheet.Select
Sheets("HOLDS").Select
Sheets("HOLDS").Copy Before:=Sheets(1)
Sheets("HOLDS (2)").Select
Sheets("HOLDS (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 = "MEDCO-65"
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Copy Before:=Sheets(1)
Sheets("MEDCO-65 (2)").Select
Sheets("MEDCO-65 (2)").Name = "AOB-65"
Sheets("AOB-65").Select
Sheets("AOB-65").Copy Before:=Sheets(1)
Sheets("AOB-65 (2)").Select
Sheets("AOB-65 (2)").Name = "STATS"
Sheets("STATS-65").Select
'###REMOVES ALL PAH TRC AND IG TRC THERAPY TYPE TO REVEAL ONLY DOUBLE SHIP THERAPY TYPES###
Sheets("DOUBLE SHIPS").Select
Dim CelNWD As Range, RngNWD As Range, iNWD As Long
Set RngNWD = Columns("V").SpecialCells(xlConstants, xlTextValues)
For iNWD = RngNWD.Count To 2 Step -1
If RngNWD(iNWD).Value = "PAH TRC" _
Or RngNWD(iNWD).Value = "IG TRC" _
Then RngNWD(iNWD).EntireRow.Delete
Next iNWD
'###REMOVES ALL ORDERS WITH AN ANSWER OF N IN COLUMN C LEAVING ONLY ORDERS ON HOLD ON THE HOLDS LIST###
Sheets("HOLDS").Select
Dim CelH As Range, RngH As Range, iH As Long
Set RngH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iH = RngH.Count To 2 Step -1
If RngH(iH).Value = "N" _
Or RngH(iH).Value = "" _
Then RngH(iH).EntireRow.Delete
Next iH
'###REMOVES ALL AOB ORDERS ON THE NO SC NOTES TAB BY LOOKING AT COLUMN J AND DELETING ROW IF J CONTAINS "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 2 Step -1
If RngSC(iSC).Value = "AOB" _
Then RngSC(iSC).EntireRow.Delete
Next iSC
'###REMOVES ANY ROW ON THE NO SC NOTE TAB THAT CONTAINS "MEDCO MAIL" IN COLUMN J AND "YES" IN COLUMN L ISOLATING ONLY MEDCO MAIL ORDERS WITHOUT SC NOTES###
Dim CelSCM As Range, RngSCM As Range, iSCM As Long
Set RngSCM = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iSCM = RngSCM.Count To 2 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 ONLY LEAVING ORDERS THAT ARE NOT ON HOLD ON THE SHEET###
Dim CelSCH As Range, RngSCH As Range, iSCH As Long
Set RngSCH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iSCH = RngSCH.Count To 2 Step -1
If RngSCH(iSCH).Value = "Y" _
Or RngSCH(iSCH).Value = "" _
Then RngSCH(iSCH).EntireRow.Delete
Next iSCH
'###REMOVES MEDCO MAIL FROM COLUMN J LEAVING ONLY AOB ORDERS ON AOB-SHEETS###
Sheets("AOB-65").Select
Dim CelAOB As Range, RngAOB As Range, iAOB As Long
Set RngAOB = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iAOB = RngAOB.Count To 2 Step -1
If RngAOB(iAOB).Value = "MEDCO MAIL" _
Then RngAOB(iAOB).EntireRow.Delete
Next iAOB
'###REMOVES HOLDS AND BLANKS FROM COLUMN C ON AOB-SHEETS###
Dim CelAOBH As Range, RngAOBH As Range, iAOBH As Long
Set RngAOBH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iAOBH = RngAOBH.Count To 2 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 ON AOB-SHEETS###
Dim CelAOBPAHIGONLY As Range, RngAOBPAHIGONLY As Range, iAOBPAHIGONLY As Long
Set RngAOBPAHIGONLY = Columns("V").SpecialCells(xlConstants, xlTextValues)
For iAOBPAHIGONLY = RngAOBPAHIGONLY.Count To 2 Step -1
Select Case Range("V" & iAOBPAHIGONLY).Value
Case "PAH TRC", "IG TRC"
'do nothing
Case Else
Rows(iAOBPAHIGONLY).Delete
End Select
Next iAOBPAHIGONLY
'###REMOVES AOB FROM COLUMN J LEAVING ONLY MEDCO ORDERS ON MEDCO-SHEETS###
Sheets("MEDCO-65").Select
Dim CelMEDCO As Range, RngMEDCO As Range, iMEDCO As Long
Set RngMEDCO = Columns("J").SpecialCells(xlConstants, xlTextValues)
For iMEDCO = RngMEDCO.Count To 2 Step -1
If RngMEDCO(iMEDCO).Value = "AOB" _
Then RngMEDCO(iMEDCO).EntireRow.Delete
Next iMEDCO
'###REMOVES HOLDS AND BLANKS FROM COLUMN C ON MEDCO-SHEETS###
Dim CelMEDCOH As Range, RngMEDCOH As Range, iMEDCOH As Long
Set RngMEDCOH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iMEDCOH = RngMEDCOH.Count To 2 Step -1
If RngMEDCOH(iMEDCOH).Value = "Y" _
Or RngMEDCOH(iMEDCOH).Value = "" _
Then RngMEDCOH(iMEDCOH).EntireRow.Delete
Next iMEDCOH
'###REMOVES ORDERS WITHOUT SC NOTES FROM MEDCO-65###
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 2 Step -1
If RngMEDCOSC(iMEDCOSC).Value = "NO" _
Or RngMEDCOSC(iMEDCOSC).Value = "" _
Then RngMEDCOSC(iMEDCOSC).EntireRow.Delete
Next iMEDCOSC
'###ADDS IN PAH MEDCO SETUP & IG MEDCO SETUP SHEETS###
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Copy Before:=Sheets("NO SC NOTES")
Sheets("MEDCO-65 (2)").Select
Sheets("MEDCO-65 (2)").Name = "IG MEDCO SETUPS"
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Copy Before:=Sheets("IG MEDCO SETUPS")
Sheets("MEDCO-65 (2)").Select
Sheets("MEDCO-65 (2)").Name = "PAH MEDCO SETUPS"
'###REMOVES ALL PAH THERAPY TYPES BY DELETING ALL ROWS WITH PAH TRC IN COLUMN U ON IG MEDCO TAB###
Sheets("IG MEDCO SETUPS").Select
Dim CelIGMEDCOIGTRC As Range, RngIGMEDCOIGTRC As Range, iIGMEDCOIGTRC As Long
Set RngIGMEDCOIGTRC = Columns("V").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 ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES ON IG MEDCO TAB###
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 UCase(Trim(Range("E" & iIGMEDCO0F).Value))
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA"
'do nothing
Case Else
If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" _
Then Rows(iIGMEDCO0F).Delete
End Select
Next iIGMEDCO0F
'###REMOVES ALL IG THERAPY TYPES BY DELETING ALL ROWS WITH IG TRC IN COLUMN U ON PAH MEDCO TAB###
Sheets("PAH MEDCO SETUPS").Select
Dim CelPAHMEDCOPAHTRC As Range, RngPAHMEDCOPAHTRC As Range, iPAHMEDCOPAHTRC As Long
Set RngPAHMEDCOPAHTRC = Columns("V").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 PAH MEDCO TAB###
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 UCase(Trim(Range("E" & iPAHMEDCO0F).Value))
Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA"
'do nothing
Case Else
If Right(Range("I" & iPAHMEDCO0F).Value, 2) <> "-0" _
Then Rows(iPAHMEDCO0F).Delete
End Select
Next iPAHMEDCO0F
'###REMOVES -0 FILLS FROM COLUMN I ON MEDCO-65###
Sheets("MEDCO-65").Select
Dim CelMEDCO0F As Range, RngMEDCO0F As Range, iMEDCO0F As Long
Set RngMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iMEDCO0F = RngMEDCO0F.Count To 2 Step -1
If RngMEDCO0F(iMEDCO0F).Value Like "*-0*" _
Then RngMEDCO0F(iMEDCO0F).EntireRow.Delete
Next iMEDCO0F
'###REMOVES VARIABLE FILLS FROM MEDCO-65 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 2 Step -1
If RngMEDCOVF(iMEDCOVF).Value = "ARAL, BERT, EPAC, EPAP, EPOP, FL50, F100, GLSA, REMO, REMP, RMIP, RMIV, VENP, VENT, ZEMA" _
Then RngMEDCOVF(iMEDCOVF).EntireRow.Delete
Next iMEDCOVF
'###ADDS IN SHEETS FOR SEPARATION OF STATES###
Sheets("AOB-65").Select
Sheets("AOB-65").Copy Before:=Sheets(2)
Sheets("AOB-65 (2)").Select
Sheets("AOB-65 (2)").Name = "AOB-24"
Sheets("AOB-24").Select
Sheets("AOB-24").Copy Before:=Sheets(2)
Sheets("AOB-24 (2)").Select
Sheets("AOB-24 (2)").Name = "AOB-NY,NJ,MS"
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Copy Before:=Sheets(5)
Sheets("MEDCO-65 (2)").Select
Sheets("MEDCO-65 (2)").Name = "MEDCO-24"
Sheets("MEDCO-24").Select
Sheets("MEDCO-24").Name = "MEDCO-24"
Sheets("MEDCO-24").Select
Sheets("MEDCO-24").Copy Before:=Sheets(5)
Sheets("MEDCO-24 (2)").Select
Sheets("MEDCO-24 (2)").Name = "MEDCO-NY,NJ,MS"
'###REMOVES ALL STATES EXCEPT NY,NJ,MS FROM AOB-NY,NJ,MS SHEET###
Sheets("AOB-NY,NJ,MS").Select
Dim CelNYNJMS As Range, RngNYNJMS As Range, iNYNJMS As Long
Set RngNYNJMS = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iNYNJMS = RngNYNJMS.Count To 2 Step -1
Select Case Range("T" & iNYNJMS).Value
Case "NY", "NJ", "MS"
'do nothing
Case Else
Rows(iNYNJMS).Delete
End Select
Next iNYNJMS
'###REMOVES ALL STATES EXCEPT NY,NJ,MS FROM MEDCO-NY,NJ,MS SHEET###
Sheets("MEDCO-NY,NJ,MS").Select
Dim CelNYNJMSMD As Range, RngNYNJMSMD As Range, iNYNJMSMD As Long
Set RngNYNJMSMD = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iNYNJMSMD = RngNYNJMSMD.Count To 2 Step -1
Select Case Range("T" & iNYNJMSMD).Value
Case "NY", "NJ", "MS"
'do nothing
Case Else
Rows(iNYNJMSMD).Delete
End Select
Next iNYNJMSMD
'###REMOVES ALL STATES EXCEPT 24 LISTED FROM AOB-24 SHEET###
Sheets("AOB-24").Select
Dim Cel24STATES As Range, Rng24STATES As Range, i24STATES As Long
Set Rng24STATES = Columns("I").SpecialCells(xlConstants, xlTextValues)
For i24STATES = Rng24STATES.Count To 2 Step -1
Select Case Range("T" & i24STATES).Value
Case "AL", "AK", "CO", "CT", "DE", "FL", "GA", "IL", "KS", "KY", "ME", "MD", "MA", "MT", "NE", "NM", "NC", "ND", "PA", "RI", "SC", "VA", "WA", "WI"
'do nothing
Case Else
Rows(i24STATES).Delete
End Select
Next i24STATES
'###REMOVES ALL STATES EXCEPT 24 LISTED FROM AOB-24 SHEET###
Sheets("MEDCO-24").Select
Dim Cel24STATESMD As Range, Rng24STATESMD As Range, i24STATESMD As Long
Set Rng24STATESMD = Columns("I").SpecialCells(xlConstants, xlTextValues)
For i24STATESMD = Rng24STATESMD.Count To 2 Step -1
Select Case Range("T" & i24STATESMD).Value
Case "AL", "AK", "CO", "CT", "DE", "FL", "GA", "IL", "KS", "KY", "ME", "MD", "MA", "MT", "NE", "NM", "NC", "ND", "PA", "RI", "SC", "VA", "WA", "WI"
'do nothing
Case Else
Rows(i24STATESMD).Delete
End Select
Next i24STATESMD
'###REMOVES ALL 24 LISTED + NY,NJ,MS FROM AOB-65 SHEET###
Sheets("AOB-65").Select
Dim Cel24STATES65 As Range, Rng24STATES65 As Range, i24STATES65 As Long
Set Rng24STATES65 = Columns("I").SpecialCells(xlConstants, xlTextValues)
For i24STATES65 = Rng24STATES65.Count To 2 Step -1
Select Case Range("T" & i24STATES65).Value
Case "AZ", "AR", "CA", "HI", "ID", "IN", "IA", "LA", "MI", "MN", "MO", "NV", "NH", "OH", "OK", "OR", "SD", "TN", "TX", "UT", "VT", "WV", "WY", ""
'do nothing
Case Else
Rows(i24STATES65).Delete
End Select
Next i24STATES65
'###REMOVES ALL 24 LISTED + NY,NJ,MS FROM MEDCO-65 SHEET###
Sheets("MEDCO-65").Select
Dim Cel24STATES65MD As Range, Rng24STATES65MD As Range, i24STATES65MD As Long
Set Rng24STATES65MD = Columns("I").SpecialCells(xlConstants, xlTextValues)
For i24STATES65MD = Rng24STATES65MD.Count To 2 Step -1
Select Case Range("T" & i24STATES65MD).Value
Case "AZ", "AR", "CA", "HI", "ID", "IN", "IA", "LA", "MI", "MN", "MO", "NV", "NH", "OH", "OK", "OR", "SD", "TN", "TX", "UT", "VT", "WV", "WY", ""
'do nothing
Case Else
Rows(i24STATES65MD).Delete
End Select
Next i24STATES65MD
'###'REMOVES HOLDS FROM STATS DATA TAB###
Sheets("STATS").Select
Dim CelSTATSH As Range, RngSTATSH As Range, iSTATSH As Long
Set RngSTATSH = Columns("C").SpecialCells(xlConstants, xlTextValues)
For iSTATSH = RngSTATSH.Count To 2 Step -1
If RngSTATSH(iSTATSH).Value = "Y" _
Then RngSTATSH(iSTATSH).EntireRow.Delete
Next iSTATSH
'###REMOVES DOUBLE SHIPS FROM STATS DATA TAB###
Dim CelSTATSTRCONLY As Range, RngSTATSTRCONLY As Range, iSTATSTRCONLY As Long
Set RngSTATSTRCONLY = Columns("I").SpecialCells(xlConstants, xlTextValues)
For iSTATSTRCONLY = RngSTATSTRCONLY.Count To 2 Step -1
Select Case Range("V" & iSTATSTRCONLY).Value
Case "PAH TRC", "IG TRC"
'do nothing
Case Else
Rows(iSTATSTRCONLY).Delete
End Select
Next iSTATSTRCONLY
'###NAMES SHEETS AS DATA SHEETS###
Sheets("AOB-NY,NJ,MS").Select
Sheets("AOB-NY,NJ,MS").Name = "AOB-NY,NJ,MS-DATA"
Sheets("AOB-24").Select
Sheets("AOB-24").Name = "AOB-24-DATA"
Sheets("AOB-65").Select
Sheets("AOB-65").Name = "AOB-65-DATA"
Sheets("MEDCO-NY,NJ,MS").Select
Sheets("MEDCO-NY,NJ,MS").Name = "MEDCO-NY,NJ,MS-DATA"
Sheets("MEDCO-24").Select
Sheets("MEDCO-24").Name = "MEDCO-24-DATA"
Sheets("MEDCO-65").Select
Sheets("MEDCO-65").Name = "MEDCO-65-DATA"
Sheets("IG MEDCO SETUPS").Select
Sheets("STATS").Select
Sheets("STATS").Name = "STATS-DATA"
Sheets("STATS-DATA").Select
'###PIVOT TABLE BUILDS###
Range("A1").Select
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", "ACTI")
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( _
"ACTI").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("Sheet12").Select
Sheets("Sheet12").Name = "STATS"
ActiveWorkbook.ShowPivotTableFieldList = False
objTable.ShowTableStyleRowStripes = True
objTable.ShowTableStyleColumnStripes = True
objTable.MergeLabels = False
objTable.PivotFields("GROUPER").ShowDetail = False
'###AOB-65###
Sheets("AOB-65-DATA").Select
Range("A1").Select
Dim objTableAOB65 As PivotTable, objFieldAOB65 As PivotField
ActiveWorkbook.Sheets("AOB-65-DATA").Select
Range("A1").Select
Set objTableAOB65 = Sheets("AOB-65-DATA").PivotTableWizard
Set objFieldAOB65 = objTableAOB65.PivotFields("TRC")
objFieldAOB65.Orientation = xlColumnField
Set objFieldAOB65 = objTableAOB65.PivotFields("DAY")
objFieldAOB65.PivotItems("PREVIOUS SHIP").Visible = True
objFieldAOB65.PivotItems("FUTURE SHIP").Visible = False
objFieldAOB65.PivotItems("NEXT DAY").Visible = False
objFieldAOB65.Orientation = xlRowField
objFieldAOB65.PivotItems( _
"SAME DAY").Position = 1
objFieldAOB65.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldAOB65.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldAOB65.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldAOB65 = objTableAOB65.PivotFields("GROUPER")
Dim varItemListAOB65() As Variant
Dim strItem1AOB65 As String
Dim iAOB65 As Long
Application.ScreenUpdating = False
varItemListAOB65 = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")
strItem1AOB65 = varItemListAOB65(LBound(varItemListAOB65))
With objTableAOB65.PivotFields("GROUPER")
.PivotItems(strItem1AOB65).Visible = True
For iAOB65 = 1 To .PivotItems.Count
If .PivotItems(iAOB65) <> strItem1AOB65 And _
.PivotItems(iAOB65).Visible = True Then
.PivotItems(iAOB65).Visible = False
End If
Next iAOB65
For iAOB65 = LBound(varItemListAOB65) + 1 To UBound(varItemListAOB65)
.PivotItems(varItemListAOB65(iAOB65)).Visible = True
Next iAOB65
End With
objFieldAOB65.Orientation = xlRowField
objFieldAOB65.PivotItems( _
"ACTI").Position = 1
objFieldAOB65.PivotItems( _
"PAH ORALS").Position = 1
objFieldAOB65.PivotItems( _
"PAH INH").Position = 1
objFieldAOB65.PivotItems( _
"PAH INJ").Position = 1
objFieldAOB65.PivotItems( _
"ALPHA-IG").Position = 1
objFieldAOB65.PivotItems( _
"HAE").Position = 1
objFieldAOB65.PivotItems( _
"ZYMES").Position = 1
objFieldAOB65.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldAOB65 = objTableAOB65.PivotFields("THERAPY TYPE")
objFieldAOB65.Orientation = xlRowField
Set objFieldAOB65 = objTableAOB65.PivotFields("RX HOME ID #")
objFieldAOB65.Orientation = xlRowField
Set objFieldAOB65 = objTableAOB65.PivotFields("RX HOME ID #")
objFieldAOB65.Orientation = xlDataField
objFieldAOB65.Function = xlCount
objFieldAOB65.NumberFormat = "Number"
objTableAOB65.RowAxisLayout xlOutlineRow
objTableAOB65.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet13").Select
Sheets("Sheet13").Name = "AOB-65"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'###AOB-65 NEXT DAY PIVOT TABLE###
Sheets("AOB-65-DATA").Select
Range("A1").Select
Dim objTableAOB65ND As PivotTable, objFieldAOB65ND As PivotField
ActiveWorkbook.Sheets("AOB-65-DATA").Select
Range("A1").Select
Set objTableAOB65ND = Sheets("AOB-65-DATA").PivotTableWizard
Set objFieldAOB65ND = objTableAOB65ND.PivotFields("TRC")
objFieldAOB65ND.Orientation = xlColumnField
Set objFieldAOB65ND = objTableAOB65ND.PivotFields("DAY")
objFieldAOB65ND.PivotItems("NEXT DAY").Visible = True
objFieldAOB65ND.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOB65ND.PivotItems("SAME DAY").Visible = False
objFieldAOB65ND.PivotItems("FUTURE SHIP").Visible = False
objFieldAOB65ND.Orientation = xlRowField
objFieldAOB65ND.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldAOB65ND = objTableAOB65ND.PivotFields("THERAPY TYPE")
Dim varItemListAOB65ND() As Variant
Dim strItem1AOB65ND As String
Dim iAOB65ND As Long
Application.ScreenUpdating = False
varItemListAOB65ND = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOB65ND = varItemListAOB65ND(LBound(varItemListAOB65ND))
With objTableAOB65ND.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOB65ND).Visible = True
For iAOB65ND = 1 To .PivotItems.Count
If .PivotItems(iAOB65ND) <> strItem1AOB65ND And _
.PivotItems(iAOB65ND).Visible = True Then
.PivotItems(iAOB65ND).Visible = False
End If
Next iAOB65ND
For iAOB65ND = LBound(varItemListAOB65ND) + 1 To UBound(varItemListAOB65ND)
.PivotItems(varItemListAOB65ND(iAOB65ND)).Visible = True
Next iAOB65ND
End With
objFieldAOB65ND.Orientation = xlRowField
objFieldAOB65ND.Orientation = xlRowField
objFieldAOB65ND.PivotItems( _
"SILD").Position = 1
objFieldAOB65ND.PivotItems( _
"REVA").Position = 1
objFieldAOB65ND.PivotItems( _
"ADCR").Position = 1
objFieldAOB65ND.PivotItems( _
"LETA").Position = 1
objFieldAOB65ND.PivotItems( _
"TPAP").Position = 1
objFieldAOB65ND.PivotItems( _
"TRAC").Position = 1
objFieldAOB65ND.PivotItems( _
"TRAB").Position = 1
objFieldAOB65ND.PivotItems( _
"REVC").Position = 1
objFieldAOB65ND.PivotItems( _
"TOBI").Position = 1
objFieldAOB65ND.PivotItems( _
"PUL").Position = 1
objFieldAOB65ND.PivotItems( _
"ACTP").Position = 1
objFieldAOB65ND.PivotItems( _
"GMPX").Position = 1
objFieldAOB65ND.PivotItems( _
"FLBO").Position = 1
objFieldAOB65ND.PivotItems( _
"IVGF").Position = 1
objFieldAOB65ND.PivotItems( _
"GKSC").Position = 1
objFieldAOB65ND.PivotItems( _
"GKIV").Position = 1
objFieldAOB65ND.PivotItems( _
"GGSC").Position = 1
objFieldAOB65ND.PivotItems( _
"GGIV").Position = 1
objFieldAOB65ND.PivotItems( _
"IVGS").Position = 1
objFieldAOB65ND.PivotItems( _
"GAMS").Position = 1
objFieldAOB65ND.PivotItems( _
"HIZA").Position = 1
objFieldAOB65ND.PivotItems( _
"IVGP").Position = 1
objFieldAOB65ND.PivotItems( _
"GAMC").Position = 1
objFieldAOB65ND.PivotItems( _
"GLSA").Position = 1
objFieldAOB65ND.PivotItems( _
"ZEMA").Position = 1
objFieldAOB65ND.PivotItems( _
"ARAL").Position = 1
objFieldAOB65ND.PivotItems( _
"REMP").Position = 1
objFieldAOB65ND.PivotItems( _
"REMO").Position = 1
objFieldAOB65ND.PivotItems( _
"RMIV").Position = 1
objFieldAOB65ND.PivotItems( _
"RMIP").Position = 1
objFieldAOB65ND.PivotItems( _
"TYVS").Position = 1
objFieldAOB65ND.PivotItems( _
"TYVP").Position = 1
objFieldAOB65ND.PivotItems( _
"TYVA").Position = 1
objFieldAOB65ND.PivotItems( _
"VENT").Position = 1
objFieldAOB65ND.PivotItems( _
"VENP").Position = 1
objFieldAOB65ND.PivotItems( _
"FPAP").Position = 1
objFieldAOB65ND.PivotItems( _
"EPOP").Position = 1
objFieldAOB65ND.PivotItems( _
"EPAP").Position = 1
objFieldAOB65ND.PivotItems( _
"EPAC").Position = 1
objFieldAOB65ND.PivotItems( _
"F100").Position = 1
objFieldAOB65ND.PivotItems( _
"FL50").Position = 1
objFieldAOB65ND.PivotItems( _
"FZYR").Position = 1
objFieldAOB65ND.PivotItems( _
"BERT").Position = 1
objFieldAOB65ND.PivotItems( _
"ELAP").Position = 1
objFieldAOB65ND.PivotItems( _
"ALDZ").Position = 1
objFieldAOB65ND.PivotItems( _
"FABZ").Position = 1
objFieldAOB65ND.PivotItems( _
"FABF").Position = 1
objFieldAOB65ND.PivotItems( _
"NGLZ").Position = 1
objFieldAOB65ND.PivotItems( _
"NGLF").Position = 1
objFieldAOB65ND.PivotItems( _
"MYOZ").Position = 1
objFieldAOB65ND.PivotItems( _
"LUMZ").Position = 1
objFieldAOB65ND.PivotItems( _
"VPRV").Position = 1
objFieldAOB65ND.PivotItems( _
"ZYMF").Position = 1
objFieldAOB65ND.PivotItems( _
"ZYME").Position = 1
Set objFieldAOB65ND = objTableAOB65ND.PivotFields("RX HOME ID #")
objFieldAOB65ND.Orientation = xlRowField
Set objFieldAOB65ND = objTableAOB65ND.PivotFields("RX HOME ID #")
objFieldAOB65ND.Orientation = xlDataField
objFieldAOB65ND.Function = xlCount
objFieldAOB65ND.NumberFormat = "Number"
objTableAOB65ND.RowAxisLayout xlOutlineRow
objTableAOB65ND.TableStyle2 = "PivotStyleMedium10"
objTableAOB65ND.Location = "'AOB-65'!$J$1"
Sheets("Sheet14").Select
Sheets("Sheet14").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-65").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
'###AOB-65 FUTURE PIVOT TABLE###
Sheets("AOB-65-DATA").Select
Range("A1").Select
Dim objTableAOB65FD As PivotTable, objFieldAOB65FD As PivotField
ActiveWorkbook.Sheets("AOB-65-DATA").Select
Range("A1").Select
Set objTableAOB65FD = Sheets("AOB-65-DATA").PivotTableWizard
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("TRC")
objFieldAOB65FD.Orientation = xlColumnField
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("DAY")
objFieldAOB65FD.PivotItems("FUTURE SHIP").Visible = True
objFieldAOB65FD.PivotItems("NEXT DAY").Visible = False
objFieldAOB65FD.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOB65FD.PivotItems("SAME DAY").Visible = False
objFieldAOB65FD.Orientation = xlRowField
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("SHIP DATE")
objFieldAOB65FD.Orientation = xlRowField
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("THERAPY TYPE")
Dim varItemListAOB65FD() As Variant
Dim strItem1AOB65FD As String
Dim iAOB65FD As Long
Application.ScreenUpdating = False
varItemListAOB65FD = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOB65FD = varItemListAOB65FD(LBound(varItemListAOB65FD))
With objTableAOB65FD.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOB65FD).Visible = True
For iAOB65FD = 1 To .PivotItems.Count
If .PivotItems(iAOB65FD) <> strItem1AOB65FD And _
.PivotItems(iAOB65FD).Visible = True Then
.PivotItems(iAOB65FD).Visible = False
End If
Next iAOB65FD
For iAOB65FD = LBound(varItemListAOB65FD) + 1 To UBound(varItemListAOB65FD)
.PivotItems(varItemListAOB65FD(iAOB65FD)).Visible = True
Next iAOB65FD
End With
objFieldAOB65FD.Orientation = xlRowField
objFieldAOB65FD.Orientation = xlRowField
objFieldAOB65FD.PivotItems( _
"SILD").Position = 1
objFieldAOB65FD.PivotItems( _
"REVA").Position = 1
objFieldAOB65FD.PivotItems( _
"ADCR").Position = 1
objFieldAOB65FD.PivotItems( _
"LETA").Position = 1
objFieldAOB65FD.PivotItems( _
"TPAP").Position = 1
objFieldAOB65FD.PivotItems( _
"TRAC").Position = 1
objFieldAOB65FD.PivotItems( _
"TRAB").Position = 1
objFieldAOB65FD.PivotItems( _
"REVC").Position = 1
objFieldAOB65FD.PivotItems( _
"TOBI").Position = 1
objFieldAOB65FD.PivotItems( _
"PUL").Position = 1
objFieldAOB65FD.PivotItems( _
"ACTP").Position = 1
objFieldAOB65FD.PivotItems( _
"GMPX").Position = 1
objFieldAOB65FD.PivotItems( _
"FLBO").Position = 1
objFieldAOB65FD.PivotItems( _
"IVGF").Position = 1
objFieldAOB65FD.PivotItems( _
"GKSC").Position = 1
objFieldAOB65FD.PivotItems( _
"GKIV").Position = 1
objFieldAOB65FD.PivotItems( _
"GGSC").Position = 1
objFieldAOB65FD.PivotItems( _
"GGIV").Position = 1
objFieldAOB65FD.PivotItems( _
"IVGS").Position = 1
objFieldAOB65FD.PivotItems( _
"GAMS").Position = 1
objFieldAOB65FD.PivotItems( _
"HIZA").Position = 1
objFieldAOB65FD.PivotItems( _
"IVGP").Position = 1
objFieldAOB65FD.PivotItems( _
"GAMC").Position = 1
objFieldAOB65FD.PivotItems( _
"GLSA").Position = 1
objFieldAOB65FD.PivotItems( _
"ZEMA").Position = 1
objFieldAOB65FD.PivotItems( _
"ARAL").Position = 1
objFieldAOB65FD.PivotItems( _
"REMP").Position = 1
objFieldAOB65FD.PivotItems( _
"REMO").Position = 1
objFieldAOB65FD.PivotItems( _
"RMIV").Position = 1
objFieldAOB65FD.PivotItems( _
"RMIP").Position = 1
objFieldAOB65FD.PivotItems( _
"TYVS").Position = 1
objFieldAOB65FD.PivotItems( _
"TYVP").Position = 1
objFieldAOB65FD.PivotItems( _
"TYVA").Position = 1
objFieldAOB65FD.PivotItems( _
"VENT").Position = 1
objFieldAOB65FD.PivotItems( _
"VENP").Position = 1
objFieldAOB65FD.PivotItems( _
"FPAP").Position = 1
objFieldAOB65FD.PivotItems( _
"EPOP").Position = 1
objFieldAOB65FD.PivotItems( _
"EPAP").Position = 1
objFieldAOB65FD.PivotItems( _
"EPAC").Position = 1
objFieldAOB65FD.PivotItems( _
"F100").Position = 1
objFieldAOB65FD.PivotItems( _
"FL50").Position = 1
objFieldAOB65FD.PivotItems( _
"FZYR").Position = 1
objFieldAOB65FD.PivotItems( _
"BERT").Position = 1
objFieldAOB65FD.PivotItems( _
"ELAP").Position = 1
objFieldAOB65FD.PivotItems( _
"ALDZ").Position = 1
objFieldAOB65FD.PivotItems( _
"FABZ").Position = 1
objFieldAOB65FD.PivotItems( _
"FABF").Position = 1
objFieldAOB65FD.PivotItems( _
"NGLZ").Position = 1
objFieldAOB65FD.PivotItems( _
"NGLF").Position = 1
objFieldAOB65FD.PivotItems( _
"MYOZ").Position = 1
objFieldAOB65FD.PivotItems( _
"LUMZ").Position = 1
objFieldAOB65FD.PivotItems( _
"VPRV").Position = 1
objFieldAOB65FD.PivotItems( _
"ZYMF").Position = 1
objFieldAOB65FD.PivotItems( _
"ZYME").Position = 1
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("RX HOME ID #")
objFieldAOB65FD.Orientation = xlRowField
Set objFieldAOB65FD = objTableAOB65FD.PivotFields("RX HOME ID #")
objFieldAOB65FD.Orientation = xlDataField
objFieldAOB65FD.Function = xlCount
objFieldAOB65FD.NumberFormat = "Number"
objTableAOB65FD.RowAxisLayout xlOutlineRow
objTableAOB65FD.TableStyle2 = "PivotStyleMedium11"
objTableAOB65FD.Location = "'AOB-65'!$R$1"
Sheets("Sheet15").Select
Sheets("Sheet15").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-65").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
'###AOB-24###
Sheets("AOB-24-DATA").Select
Range("A1").Select
Dim objTableAOB24 As PivotTable, objFieldAOB24 As PivotField
ActiveWorkbook.Sheets("AOB-24-DATA").Select
Range("A1").Select
Set objTableAOB24 = Sheets("AOB-24-DATA").PivotTableWizard
Set objFieldAOB24 = objTableAOB24.PivotFields("TRC")
objFieldAOB24.Orientation = xlColumnField
Set objFieldAOB24 = objTableAOB24.PivotFields("DAY")
objFieldAOB24.PivotItems("PREVIOUS SHIP").Visible = True
objFieldAOB24.PivotItems("FUTURE SHIP").Visible = False
objFieldAOB24.PivotItems("NEXT DAY").Visible = False
objFieldAOB24.Orientation = xlRowField
objFieldAOB24.PivotItems( _
"SAME DAY").Position = 1
objFieldAOB24.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldAOB24.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldAOB24.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldAOB24 = objTableAOB24.PivotFields("GROUPER")
Dim varItemListAOB24() As Variant
Dim strItem1AOB24 As String
Dim iAOB24 As Long
Application.ScreenUpdating = False
varItemListAOB24 = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")
strItem1AOB24 = varItemListAOB24(LBound(varItemListAOB24))
With objTableAOB24.PivotFields("GROUPER")
.PivotItems(strItem1AOB24).Visible = True
For iAOB24 = 1 To .PivotItems.Count
If .PivotItems(iAOB24) <> strItem1AOB24 And _
.PivotItems(iAOB24).Visible = True Then
.PivotItems(iAOB24).Visible = False
End If
Next iAOB24
For iAOB24 = LBound(varItemListAOB24) + 1 To UBound(varItemListAOB24)
.PivotItems(varItemListAOB24(iAOB24)).Visible = True
Next iAOB24
End With
objFieldAOB24.Orientation = xlRowField
objFieldAOB24.PivotItems( _
"ACTI").Position = 1
objFieldAOB24.PivotItems( _
"PAH ORALS").Position = 1
objFieldAOB24.PivotItems( _
"PAH INH").Position = 1
objFieldAOB24.PivotItems( _
"PAH INJ").Position = 1
objFieldAOB24.PivotItems( _
"ALPHA-IG").Position = 1
objFieldAOB24.PivotItems( _
"HAE").Position = 1
objFieldAOB24.PivotItems( _
"ZYMES").Position = 1
objFieldAOB24.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldAOB24 = objTableAOB24.PivotFields("THERAPY TYPE")
objFieldAOB24.Orientation = xlRowField
Set objFieldAOB24 = objTableAOB24.PivotFields("RX HOME ID #")
objFieldAOB24.Orientation = xlRowField
Set objFieldAOB24 = objTableAOB24.PivotFields("RX HOME ID #")
objFieldAOB24.Orientation = xlDataField
objFieldAOB24.Function = xlCount
objFieldAOB24.NumberFormat = "Number"
objTableAOB24.RowAxisLayout xlOutlineRow
objTableAOB24.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet16").Select
Sheets("Sheet16").Name = "AOB-24"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'###AOB-24 NEXT DAY PIVOT TABLE###
Sheets("AOB-24-DATA").Select
Range("A1").Select
Dim objTableAOB24ND As PivotTable, objFieldAOB24ND As PivotField
ActiveWorkbook.Sheets("AOB-24-DATA").Select
Range("A1").Select
Set objTableAOB24ND = Sheets("AOB-24-DATA").PivotTableWizard
Set objFieldAOB24ND = objTableAOB24ND.PivotFields("TRC")
objFieldAOB24ND.Orientation = xlColumnField
Set objFieldAOB24ND = objTableAOB24ND.PivotFields("DAY")
objFieldAOB24ND.PivotItems("NEXT DAY").Visible = True
objFieldAOB24ND.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOB24ND.PivotItems("SAME DAY").Visible = False
objFieldAOB24ND.PivotItems("FUTURE SHIP").Visible = False
objFieldAOB24ND.Orientation = xlRowField
objFieldAOB24ND.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldAOB24ND = objTableAOB24ND.PivotFields("THERAPY TYPE")
Dim varItemListAOB24ND() As Variant
Dim strItem1AOB24ND As String
Dim iAOB24ND As Long
Application.ScreenUpdating = False
varItemListAOB24ND = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOB24ND = varItemListAOB24ND(LBound(varItemListAOB24ND))
With objTableAOB24ND.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOB24ND).Visible = True
For iAOB24ND = 1 To .PivotItems.Count
If .PivotItems(iAOB24ND) <> strItem1AOB24ND And _
.PivotItems(iAOB24ND).Visible = True Then
.PivotItems(iAOB24ND).Visible = False
End If
Next iAOB24ND
For iAOB24ND = LBound(varItemListAOB24ND) + 1 To UBound(varItemListAOB24ND)
.PivotItems(varItemListAOB24ND(iAOB24ND)).Visible = True
Next iAOB24ND
End With
objFieldAOB24ND.Orientation = xlRowField
objFieldAOB24ND.Orientation = xlRowField
objFieldAOB24ND.PivotItems( _
"SILD").Position = 1
objFieldAOB24ND.PivotItems( _
"REVA").Position = 1
objFieldAOB24ND.PivotItems( _
"ADCR").Position = 1
objFieldAOB24ND.PivotItems( _
"LETA").Position = 1
objFieldAOB24ND.PivotItems( _
"TPAP").Position = 1
objFieldAOB24ND.PivotItems( _
"TRAC").Position = 1
objFieldAOB24ND.PivotItems( _
"TRAB").Position = 1
objFieldAOB24ND.PivotItems( _
"REVC").Position = 1
objFieldAOB24ND.PivotItems( _
"TOBI").Position = 1
objFieldAOB24ND.PivotItems( _
"PUL").Position = 1
objFieldAOB24ND.PivotItems( _
"ACTP").Position = 1
objFieldAOB24ND.PivotItems( _
"GMPX").Position = 1
objFieldAOB24ND.PivotItems( _
"FLBO").Position = 1
objFieldAOB24ND.PivotItems( _
"IVGF").Position = 1
objFieldAOB24ND.PivotItems( _
"GKSC").Position = 1
objFieldAOB24ND.PivotItems( _
"GKIV").Position = 1
objFieldAOB24ND.PivotItems( _
"GGSC").Position = 1
objFieldAOB24ND.PivotItems( _
"GGIV").Position = 1
objFieldAOB24ND.PivotItems( _
"IVGS").Position = 1
objFieldAOB24ND.PivotItems( _
"GAMS").Position = 1
objFieldAOB24ND.PivotItems( _
"HIZA").Position = 1
objFieldAOB24ND.PivotItems( _
"IVGP").Position = 1
objFieldAOB24ND.PivotItems( _
"GAMC").Position = 1
objFieldAOB24ND.PivotItems( _
"GLSA").Position = 1
objFieldAOB24ND.PivotItems( _
"ZEMA").Position = 1
objFieldAOB24ND.PivotItems( _
"ARAL").Position = 1
objFieldAOB24ND.PivotItems( _
"REMP").Position = 1
objFieldAOB24ND.PivotItems( _
"REMO").Position = 1
objFieldAOB24ND.PivotItems( _
"RMIV").Position = 1
objFieldAOB24ND.PivotItems( _
"RMIP").Position = 1
objFieldAOB24ND.PivotItems( _
"TYVS").Position = 1
objFieldAOB24ND.PivotItems( _
"TYVP").Position = 1
objFieldAOB24ND.PivotItems( _
"TYVA").Position = 1
objFieldAOB24ND.PivotItems( _
"VENT").Position = 1
objFieldAOB24ND.PivotItems( _
"VENP").Position = 1
objFieldAOB24ND.PivotItems( _
"FPAP").Position = 1
objFieldAOB24ND.PivotItems( _
"EPOP").Position = 1
objFieldAOB24ND.PivotItems( _
"EPAP").Position = 1
objFieldAOB24ND.PivotItems( _
"EPAC").Position = 1
objFieldAOB24ND.PivotItems( _
"F100").Position = 1
objFieldAOB24ND.PivotItems( _
"FL50").Position = 1
objFieldAOB24ND.PivotItems( _
"FZYR").Position = 1
objFieldAOB24ND.PivotItems( _
"BERT").Position = 1
objFieldAOB24ND.PivotItems( _
"ELAP").Position = 1
objFieldAOB24ND.PivotItems( _
"ALDZ").Position = 1
objFieldAOB24ND.PivotItems( _
"FABZ").Position = 1
objFieldAOB24ND.PivotItems( _
"FABF").Position = 1
objFieldAOB24ND.PivotItems( _
"NGLZ").Position = 1
objFieldAOB24ND.PivotItems( _
"NGLF").Position = 1
objFieldAOB24ND.PivotItems( _
"MYOZ").Position = 1
objFieldAOB24ND.PivotItems( _
"LUMZ").Position = 1
objFieldAOB24ND.PivotItems( _
"VPRV").Position = 1
objFieldAOB24ND.PivotItems( _
"ZYMF").Position = 1
objFieldAOB24ND.PivotItems( _
"ZYME").Position = 1
Set objFieldAOB24ND = objTableAOB24ND.PivotFields("RX HOME ID #")
objFieldAOB24ND.Orientation = xlRowField
Set objFieldAOB24ND = objTableAOB24ND.PivotFields("RX HOME ID #")
objFieldAOB24ND.Orientation = xlDataField
objFieldAOB24ND.Function = xlCount
objFieldAOB24ND.NumberFormat = "Number"
objTableAOB24ND.RowAxisLayout xlOutlineRow
objTableAOB24ND.TableStyle2 = "PivotStyleMedium10"
objTableAOB24ND.Location = "'AOB-24'!$J$1"
Sheets("Sheet17").Select
Sheets("Sheet17").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-24").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
'###AOB-24 FUTURE PIVOT TABLE###
Sheets("AOB-24-DATA").Select
Range("A1").Select
Dim objTableAOB24FD As PivotTable, objFieldAOB24FD As PivotField
ActiveWorkbook.Sheets("AOB-24-DATA").Select
Range("A1").Select
Set objTableAOB24FD = Sheets("AOB-24-DATA").PivotTableWizard
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("TRC")
objFieldAOB24FD.Orientation = xlColumnField
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("DAY")
objFieldAOB24FD.PivotItems("FUTURE SHIP").Visible = True
objFieldAOB24FD.PivotItems("NEXT DAY").Visible = False
objFieldAOB24FD.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOB24FD.PivotItems("SAME DAY").Visible = False
objFieldAOB24FD.Orientation = xlRowField
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("SHIP DATE")
objFieldAOB24FD.Orientation = xlRowField
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("THERAPY TYPE")
Dim varItemListAOB24FD() As Variant
Dim strItem1AOB24FD As String
Dim iAOB24FD As Long
Application.ScreenUpdating = False
varItemListAOB24FD = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOB24FD = varItemListAOB24FD(LBound(varItemListAOB24FD))
With objTableAOB24FD.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOB24FD).Visible = True
For iAOB24FD = 1 To .PivotItems.Count
If .PivotItems(iAOB24FD) <> strItem1AOB24FD And _
.PivotItems(iAOB24FD).Visible = True Then
.PivotItems(iAOB24FD).Visible = False
End If
Next iAOB24FD
For iAOB24FD = LBound(varItemListAOB24FD) + 1 To UBound(varItemListAOB24FD)
.PivotItems(varItemListAOB24FD(iAOB24FD)).Visible = True
Next iAOB24FD
End With
objFieldAOB24FD.Orientation = xlRowField
objFieldAOB24FD.Orientation = xlRowField
objFieldAOB24FD.PivotItems( _
"SILD").Position = 1
objFieldAOB24FD.PivotItems( _
"REVA").Position = 1
objFieldAOB24FD.PivotItems( _
"ADCR").Position = 1
objFieldAOB24FD.PivotItems( _
"LETA").Position = 1
objFieldAOB24FD.PivotItems( _
"TPAP").Position = 1
objFieldAOB24FD.PivotItems( _
"TRAC").Position = 1
objFieldAOB24FD.PivotItems( _
"TRAB").Position = 1
objFieldAOB24FD.PivotItems( _
"REVC").Position = 1
objFieldAOB24FD.PivotItems( _
"TOBI").Position = 1
objFieldAOB24FD.PivotItems( _
"PUL").Position = 1
objFieldAOB24FD.PivotItems( _
"ACTP").Position = 1
objFieldAOB24FD.PivotItems( _
"GMPX").Position = 1
objFieldAOB24FD.PivotItems( _
"FLBO").Position = 1
objFieldAOB24FD.PivotItems( _
"IVGF").Position = 1
objFieldAOB24FD.PivotItems( _
"GKSC").Position = 1
objFieldAOB24FD.PivotItems( _
"GKIV").Position = 1
objFieldAOB24FD.PivotItems( _
"GGSC").Position = 1
objFieldAOB24FD.PivotItems( _
"GGIV").Position = 1
objFieldAOB24FD.PivotItems( _
"IVGS").Position = 1
objFieldAOB24FD.PivotItems( _
"GAMS").Position = 1
objFieldAOB24FD.PivotItems( _
"HIZA").Position = 1
objFieldAOB24FD.PivotItems( _
"IVGP").Position = 1
objFieldAOB24FD.PivotItems( _
"GAMC").Position = 1
objFieldAOB24FD.PivotItems( _
"GLSA").Position = 1
objFieldAOB24FD.PivotItems( _
"ZEMA").Position = 1
objFieldAOB24FD.PivotItems( _
"ARAL").Position = 1
objFieldAOB24FD.PivotItems( _
"REMP").Position = 1
objFieldAOB24FD.PivotItems( _
"REMO").Position = 1
objFieldAOB24FD.PivotItems( _
"RMIV").Position = 1
objFieldAOB24FD.PivotItems( _
"RMIP").Position = 1
objFieldAOB24FD.PivotItems( _
"TYVS").Position = 1
objFieldAOB24FD.PivotItems( _
"TYVP").Position = 1
objFieldAOB24FD.PivotItems( _
"TYVA").Position = 1
objFieldAOB24FD.PivotItems( _
"VENT").Position = 1
objFieldAOB24FD.PivotItems( _
"VENP").Position = 1
objFieldAOB24FD.PivotItems( _
"FPAP").Position = 1
objFieldAOB24FD.PivotItems( _
"EPOP").Position = 1
objFieldAOB24FD.PivotItems( _
"EPAP").Position = 1
objFieldAOB24FD.PivotItems( _
"EPAC").Position = 1
objFieldAOB24FD.PivotItems( _
"F100").Position = 1
objFieldAOB24FD.PivotItems( _
"FL50").Position = 1
objFieldAOB24FD.PivotItems( _
"FZYR").Position = 1
objFieldAOB24FD.PivotItems( _
"BERT").Position = 1
objFieldAOB24FD.PivotItems( _
"ELAP").Position = 1
objFieldAOB24FD.PivotItems( _
"ALDZ").Position = 1
objFieldAOB24FD.PivotItems( _
"FABZ").Position = 1
objFieldAOB24FD.PivotItems( _
"FABF").Position = 1
objFieldAOB24FD.PivotItems( _
"NGLZ").Position = 1
objFieldAOB24FD.PivotItems( _
"NGLF").Position = 1
objFieldAOB24FD.PivotItems( _
"MYOZ").Position = 1
objFieldAOB24FD.PivotItems( _
"LUMZ").Position = 1
objFieldAOB24FD.PivotItems( _
"VPRV").Position = 1
objFieldAOB24FD.PivotItems( _
"ZYMF").Position = 1
objFieldAOB24FD.PivotItems( _
"ZYME").Position = 1
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("RX HOME ID #")
objFieldAOB24FD.Orientation = xlRowField
Set objFieldAOB24FD = objTableAOB24FD.PivotFields("RX HOME ID #")
objFieldAOB24FD.Orientation = xlDataField
objFieldAOB24FD.Function = xlCount
objFieldAOB24FD.NumberFormat = "Number"
objTableAOB24FD.RowAxisLayout xlOutlineRow
objTableAOB24FD.TableStyle2 = "PivotStyleMedium11"
objTableAOB24FD.Location = "'AOB-24'!$R$1"
Sheets("Sheet18").Select
Sheets("Sheet18").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-24").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
'###AOB-NY,NJ,MS###
Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Dim objTableAOBNYNJMS As PivotTable, objFieldAOBNYNJMS As PivotField
ActiveWorkbook.Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Set objTableAOBNYNJMS = Sheets("AOB-NY,NJ,MS-DATA").PivotTableWizard
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("TRC")
objFieldAOBNYNJMS.Orientation = xlColumnField
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("DAY")
objFieldAOBNYNJMS.PivotItems("PREVIOUS SHIP").Visible = True
objFieldAOBNYNJMS.PivotItems("FUTURE SHIP").Visible = False
objFieldAOBNYNJMS.PivotItems("NEXT DAY").Visible = False
objFieldAOBNYNJMS.Orientation = xlRowField
objFieldAOBNYNJMS.PivotItems( _
"SAME DAY").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"PREVIOUS SHIP").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"SUNDAY SHIP").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"SATURDAY SHIP").Position = 1
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("GROUPER")
Dim varItemListAOBNYNJMS() As Variant
Dim strItem1AOBNYNJMS As String
Dim iAOBNYNJMS As Long
Application.ScreenUpdating = False
varItemListAOBNYNJMS = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")
strItem1AOBNYNJMS = varItemListAOBNYNJMS(LBound(varItemListAOBNYNJMS))
With objTableAOBNYNJMS.PivotFields("GROUPER")
.PivotItems(strItem1AOBNYNJMS).Visible = True
For iAOBNYNJMS = 1 To .PivotItems.Count
If .PivotItems(iAOBNYNJMS) <> strItem1AOBNYNJMS And _
.PivotItems(iAOBNYNJMS).Visible = True Then
.PivotItems(iAOBNYNJMS).Visible = False
End If
Next iAOBNYNJMS
For iAOBNYNJMS = LBound(varItemListAOBNYNJMS) + 1 To UBound(varItemListAOBNYNJMS)
.PivotItems(varItemListAOBNYNJMS(iAOBNYNJMS)).Visible = True
Next iAOBNYNJMS
End With
objFieldAOBNYNJMS.Orientation = xlRowField
objFieldAOBNYNJMS.PivotItems( _
"ACTI").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"PAH ORALS").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"PAH INH").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"PAH INJ").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"ALPHA-IG").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"HAE").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"ZYMES").Position = 1
objFieldAOBNYNJMS.PivotItems( _
"REVA SUSP").Position = 1
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("THERAPY TYPE")
objFieldAOBNYNJMS.Orientation = xlRowField
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("RX HOME ID #")
objFieldAOBNYNJMS.Orientation = xlRowField
Set objFieldAOBNYNJMS = objTableAOBNYNJMS.PivotFields("RX HOME ID #")
objFieldAOBNYNJMS.Orientation = xlDataField
objFieldAOBNYNJMS.Function = xlCount
objFieldAOBNYNJMS.NumberFormat = "Number"
objTableAOBNYNJMS.RowAxisLayout xlOutlineRow
objTableAOBNYNJMS.TableStyle2 = "PivotStyleMedium9"
Sheets("Sheet19").Select
Sheets("Sheet19").Name = "AOB-NY,NJ,MS"
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
'###AOB-NY,NJ,MS NEXT DAY PIVOT TABLE###
Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Dim objTableAOBNYNJMSND As PivotTable, objFieldAOBNYNJMSND As PivotField
ActiveWorkbook.Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Set objTableAOBNYNJMSND = Sheets("AOB-NY,NJ,MS-DATA").PivotTableWizard
Set objFieldAOBNYNJMSND = objTableAOBNYNJMSND.PivotFields("TRC")
objFieldAOBNYNJMSND.Orientation = xlColumnField
Set objFieldAOBNYNJMSND = objTableAOBNYNJMSND.PivotFields("DAY")
objFieldAOBNYNJMSND.PivotItems("NEXT DAY").Visible = True
objFieldAOBNYNJMSND.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOBNYNJMSND.PivotItems("SAME DAY").Visible = False
objFieldAOBNYNJMSND.PivotItems("FUTURE SHIP").Visible = False
objFieldAOBNYNJMSND.Orientation = xlRowField
objFieldAOBNYNJMSND.PivotItems( _
"NEXT DAY").Position = 1
Set objFieldAOBNYNJMSND = objTableAOBNYNJMSND.PivotFields("THERAPY TYPE")
Dim varItemListAOBNYNJMSND() As Variant
Dim strItem1AOBNYNJMSND As String
Dim iAOBNYNJMSND As Long
Application.ScreenUpdating = False
varItemListAOBNYNJMSND = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOBNYNJMSND = varItemListAOBNYNJMSND(LBound(varItemListAOBNYNJMSND))
With objTableAOBNYNJMSND.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOBNYNJMSND).Visible = True
For iAOBNYNJMSND = 1 To .PivotItems.Count
If .PivotItems(iAOBNYNJMSND) <> strItem1AOBNYNJMSND And _
.PivotItems(iAOBNYNJMSND).Visible = True Then
.PivotItems(iAOBNYNJMSND).Visible = False
End If
Next iAOBNYNJMSND
For iAOBNYNJMSND = LBound(varItemListAOBNYNJMSND) + 1 To UBound(varItemListAOBNYNJMSND)
.PivotItems(varItemListAOBNYNJMSND(iAOBNYNJMSND)).Visible = True
Next iAOBNYNJMSND
End With
objFieldAOBNYNJMSND.Orientation = xlRowField
objFieldAOBNYNJMSND.Orientation = xlRowField
objFieldAOBNYNJMSND.PivotItems( _
"SILD").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"REVA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ADCR").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"LETA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TPAP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TRAC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TRAB").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"REVC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TOBI").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"PUL").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ACTP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GMPX").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FLBO").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"IVGF").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GKSC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GKIV").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GGSC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GGIV").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"IVGS").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GAMS").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"HIZA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"IVGP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GAMC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"GLSA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ZEMA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ARAL").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"REMP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"REMO").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"RMIV").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"RMIP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TYVS").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TYVP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"TYVA").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"VENT").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"VENP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FPAP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"EPOP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"EPAP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"EPAC").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"F100").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FL50").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FZYR").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"BERT").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ELAP").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ALDZ").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FABZ").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"FABF").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"NGLZ").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"NGLF").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"MYOZ").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"LUMZ").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"VPRV").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ZYMF").Position = 1
objFieldAOBNYNJMSND.PivotItems( _
"ZYME").Position = 1
Set objFieldAOBNYNJMSND = objTableAOBNYNJMSND.PivotFields("RX HOME ID #")
objFieldAOBNYNJMSND.Orientation = xlRowField
Set objFieldAOBNYNJMSND = objTableAOBNYNJMSND.PivotFields("RX HOME ID #")
objFieldAOBNYNJMSND.Orientation = xlDataField
objFieldAOBNYNJMSND.Function = xlCount
objFieldAOBNYNJMSND.NumberFormat = "Number"
objTableAOBNYNJMSND.RowAxisLayout xlOutlineRow
objTableAOBNYNJMSND.TableStyle2 = "PivotStyleMedium10"
objTableAOBNYNJMSND.Location = "'AOB-NY,NJ,MS'!$J$1"
Sheets("Sheet20").Select
Sheets("Sheet20").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-NYNJMS").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
'###AOB-NY,NJ,MS FUTURE PIVOT TABLE###
Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Dim objTableAOBNYNJMSFD As PivotTable, objFieldAOBNYNJMSFD As PivotField
ActiveWorkbook.Sheets("AOB-NY,NJ,MS-DATA").Select
Range("A1").Select
Set objTableAOBNYNJMSFD = Sheets("AOB-NY,NJ,MS-DATA").PivotTableWizard
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("TRC")
objFieldAOBNYNJMSFD.Orientation = xlColumnField
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("DAY")
objFieldAOBNYNJMSFD.PivotItems("FUTURE SHIP").Visible = True
objFieldAOBNYNJMSFD.PivotItems("NEXT DAY").Visible = False
objFieldAOBNYNJMSFD.PivotItems("PREVIOUS SHIP").Visible = False
objFieldAOBNYNJMSFD.PivotItems("SAME DAY").Visible = False
objFieldAOBNYNJMSFD.Orientation = xlRowField
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("SHIP DATE")
objFieldAOBNYNJMSFD.Orientation = xlRowField
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("THERAPY TYPE")
Dim varItemListAOBNYNJMSFD() As Variant
Dim strItem1AOBNYNJMSFD As String
Dim iAOBNYNJMSFD As Long
Application.ScreenUpdating = False
varItemListAOBNYNJMSFD = Array("SILD", "REVA", "REVG", "REGC", "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")
strItem1AOBNYNJMSFD = varItemListAOBNYNJMSFD(LBound(varItemListAOBNYNJMSFD))
With objTableAOBNYNJMSFD.PivotFields("THERAPY TYPE")
.PivotItems(strItem1AOBNYNJMSFD).Visible = True
For iAOBNYNJMSFD = 1 To .PivotItems.Count
If .PivotItems(iAOBNYNJMSFD) <> strItem1AOBNYNJMSFD And _
.PivotItems(iAOBNYNJMSFD).Visible = True Then
.PivotItems(iAOBNYNJMSFD).Visible = False
End If
Next iAOBNYNJMSFD
For iAOBNYNJMSFD = LBound(varItemListAOBNYNJMSFD) + 1 To UBound(varItemListAOBNYNJMSFD)
.PivotItems(varItemListAOBNYNJMSFD(iAOBNYNJMSFD)).Visible = True
Next iAOBNYNJMSFD
End With
objFieldAOBNYNJMSFD.Orientation = xlRowField
objFieldAOBNYNJMSFD.Orientation = xlRowField
objFieldAOBNYNJMSFD.PivotItems( _
"SILD").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"REVA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ADCR").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"LETA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TPAP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TRAC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TRAB").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"REVC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TOBI").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"PUL").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ACTP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GMPX").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FLBO").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"IVGF").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GKSC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GKIV").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GGSC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GGIV").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"IVGS").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GAMS").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"HIZA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"IVGP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GAMC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"GLSA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ZEMA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ARAL").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"REMP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"REMO").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"RMIV").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"RMIP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TYVS").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TYVP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"TYVA").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"VENT").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"VENP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FPAP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"EPOP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"EPAP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"EPAC").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"F100").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FL50").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FZYR").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"BERT").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ELAP").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ALDZ").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FABZ").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"FABF").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"NGLZ").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"NGLF").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"MYOZ").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"LUMZ").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"VPRV").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ZYMF").Position = 1
objFieldAOBNYNJMSFD.PivotItems( _
"ZYME").Position = 1
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("RX HOME ID #")
objFieldAOBNYNJMSFD.Orientation = xlRowField
Set objFieldAOBNYNJMSFD = objTableAOBNYNJMSFD.PivotFields("RX HOME ID #")
objFieldAOBNYNJMSFD.Orientation = xlDataField
objFieldAOBNYNJMSFD.Function = xlCount
objFieldAOBNYNJMSFD.NumberFormat = "Number"
objTableAOBNYNJMSFD.RowAxisLayout xlOutlineRow
objTableAOBNYNJMSFD.TableStyle2 = "PivotStyleMedium11"
objTableAOBNYNJMSFD.Location = "'AOB-NY,NJ,MS'!$R$1"
Sheets("Sheet21").Select
Sheets("Sheet21").Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("AOB-NYNJMS").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
End Sub
