Sub DP_Report()
'
' DP_Report Macro
' Convert DP text files to Excel, formats and copies to template.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim LastRow As Long
Dim Day As String
Day = InputBox("Date of File (mmddyy):")
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
' Opening Text Files
Workbooks.OpenText Filename:= _
"\\Drive\Folder\dp" & Day & "_113.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
"\\Drive\Folder\dp" & Day & "_2856.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
"\\Drive\Folder\dp" & Day & "_5298.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
TrailingMinusNumbers:=True
' Insert Org ID
Windows("dp" & Day & "_113.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1000113"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
Windows("dp" & Day & "_2856.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1002856"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
Windows("dp" & Day & "_5298.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "1005298"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
' Copy to 113 sheet
Windows("dp" & Day & "_2856.txt").Activate
ActiveSheet.UsedRange.Copy
Windows("dp" & Day & "_113.txt").Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("dp" & Day & "_5298.txt").Activate
ActiveSheet.UsedRange.Copy
Windows("dp" & Day & "_113.txt").Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
' Closing Extra Windows
Windows("dp" & Day & "_2856.txt").Activate
ActiveWindow.Close
Windows("dp" & Day & "_5298.txt").Activate
ActiveWindow.Close
' Insert Report Type
Windows("dp" & Day & "_113.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = "DP"
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).FillDown
' Moving Columns
Range("A1").EntireColumn.Insert
Range("H1").EntireColumn.Copy
Range("A1").EntireColumn.PasteSpecial
Range("C1").EntireColumn.Insert
Range("E1").EntireColumn.Copy
Range("C1").EntireColumn.PasteSpecial
' Move to Template Sheet
ActiveSheet.UsedRange.Copy
Windows("Report_Comparison_Template_1.3.xlsm").Activate
ActiveSheet.Paste
' Close 113 Window
Windows("dp" & Day & "_113.txt").Activate
ActiveWindow.Close
Bookmarks