Sub Aut
pen()
'
' Macro3 Macro
' Macro recorded 03/13/2002 by Aetna Inc.
'
'
Application.ScreenUpdating = False
Application.StatusBar = "Macro Running in Background Please Wait....."
Workbooks.OpenText Filename:="C:ERA_EFTEPDB_BILLING.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1))
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Unit #"
Range("F1").Select
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "EPDB Provider Name"
Range("F2").Select
Columns("F:F").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("G2").Select
Columns("G:G").EntireColumn.AutoFit
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Bldg NM"
Range("H2").Select
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("I1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 1"
Range("J1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 2"
Range("J2").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("K1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr City"
Range("L1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr State"
Range("M1").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Zip"
Range("N1").Select
Columns("M:M").EntireColumn.AutoFit
Columns("M:M").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=-3
ActiveWindow.LargeScroll ToRight:=-1
Range("D1").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll ToRight:=2
Range("D1:M1").Select
Selection.Font.Bold = True
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=8
ActiveWindow.ScrollColumn = 1
Cells.Select
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.ScrollColumn = 8
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Key2:=Range("J2") _
, Order2:=xlAscending, Key3:=Range("I2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D3:M3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("A:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 160
End With
ActiveWindow.LargeScroll Down:=0
Columns("F:F").ColumnWidth = 75.71
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 38.57
ActiveWindow.LargeScroll Down:=23
Range("I791").Select
ActiveWindow.LargeScroll Down:=-23
ActiveWindow.SmallScroll ToRight:=6
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-3
Columns("E:E").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 160
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("H:H").ColumnWidth = 28.29
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").ColumnWidth = 27.71
ActiveWindow.SmallScroll ToRight:=1
Columns("J:J").ColumnWidth = 24.86
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.LargeScroll ToRight:=-2
Range("D5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("D1").Select
ActiveWindow.LargeScroll ToRight:=1
Range("D1:M4").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
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D6").Select
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
ActiveWindow.SmallScroll ToRight:=1
Range("E5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("G:G").ColumnWidth = 17.57
ActiveWindow.SmallScroll ToRight:=-2
Range("G4").Select
Selection.EntireRow.Insert
Range("G5").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Address"
Range("G6").Select
ActiveCell.FormulaR1C1 = "Number"
Range("G5:M5").Select
ActiveWindow.LargeScroll ToRight:=-2
Range("G5").Select
Selection.ClearContents
Rows("5:5").Select
Range("D5").Activate
Selection.Delete Shift:=xlUp
Range("G5").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("H9").Select
Columns("G:G").ColumnWidth = 18.29
Columns("G:G").ColumnWidth = 18
Range("D3:M3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE("" Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D4").Select
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = Selection.Address
Application.StatusBar = False
Range("D4").Select
End Sub