Sub ALCOHOLPICK()
Application.ScreenUpdating = False
ChDir "C:\Documents and Settings\PAULW\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\PAULW\Desktop\ALCOHOL MASTER.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(5, 1), Array(13, 1), Array(18, 1), Array(43, 1), Array(49, 1), Array(53, 1), Array _
(59, 1), Array(66, 1), Array(72, 1), Array(78, 1), Array(92, 1), Array(100, 1), Array(105, 1 _
), Array(113, 1), Array(120, 1), Array(125, 1))
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("ALCOHOL MASTER")
Dim rstart As Long
Dim rEnd As Long
Dim r As Long
Dim i As Long
Dim LResult1 As String
Dim Lresult2 As String
Dim Txt1 As String
Dim Txt2 As String
Dim ColA As String
Dim ColC As String
Dim ColH As Long
rstart = 1
rEnd = ws1.Cells(Rows.Count, 1).End(xlUp).Row
r = rEnd
ws1.Select
LResult1 = ""
Lresult2 = ""
Txt1 = ""
Txt2 = ""
ColA = ""
ColC = ""
ColH = 0
For i = 1 To r
ColA = Range("A" & rstart + i).Value
If Left(ColA, 1) = "-" Then
Range("B" & rstart + i).Select
'ActiveCell.FormulaR1C1 = "Found Dotted Line"
Rows(rstart + i & ":" & rstart + i).Select
Selection.Delete Shift:=xlUp
End If
Next i
Dim vList, lArrCounter As Long
Dim rFOUND As Range, delRNG As Range, rFIRST As String
vList = Array("Deli", "O R D E R E D", "G Description", "Produc", "Suppl", ".ORDPRODS v10.81 ORDERED", "ANALY", "REPTS", "Book", "Fall")
For lArrCounter = LBound(vList) To UBound(vList)
With Worksheets("ALCOHOL MASTER").UsedRange
Set rFOUND = .Find( _
What:=vList(lArrCounter), _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rFOUND Is Nothing Then
rFIRST = rFOUND.Address
Do
If delRNG Is Nothing Then
Set delRNG = Range("A" & rFOUND.Row)
Else
Set delRNG = Union(delRNG, Range("A" & rFOUND.Row))
End If
Set rFOUND = .FindNext(After:=rFOUND)
Loop Until rFOUND.Address = rFIRST
End If
Set rFOUND = Nothing
End With
Next lArrCounter
If Not delRNG Is Nothing Then delRNG.EntireRow.Delete
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF(MOD(ROW(),1)=1, """", R[-1]C[-15])"
Range("R2").Select
Selection.Copy
Range("R2:R500").Select
ActiveSheet.Paste
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:20").Select
Selection.Columns.AutoFit
Selection.Sort Key1:=Range("O2"), Order1:=xlAscending, Key2:=Range("Q2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=15, Criteria1:="Trans"
Rows("140:388").Select
Range("C93").Activate
Selection.Delete Shift:=xlUp
Columns("K:L").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
Application.ScreenUpdating = False
ActiveSheet.PageSetup.PrintArea = ""
Cells.Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=15, Criteria1:="Trans"
.PrintTitleRows = ""
.PrintTitleColumns = ""
Range("A1").Select
ActiveCell.FormulaR1C1 = "Supp"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Bin"
Range("C1").Select
ActiveCell.FormulaR1C1 = "SngC"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Product"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Pack"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Ord"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Bal"
Range("J1").Select
ActiveCell.FormulaR1C1 = ""
Range("M1").Select
ActiveCell.FormulaR1C1 = "Conv"
Range("N1").Select
ActiveCell.FormulaR1C1 = "+/-"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Act"
Range("P1").Select
ActiveCell.FormulaR1C1 = "+/-"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "CBin"
Range("R1").Select
ActiveCell.FormulaR1C1 = "C.Code"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = False
Columns("A:R").Select
Selection.Columns.AutoFit
Columns("K:L").Select
Selection.EntireColumn.Hidden = True
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
Dim c As Range
i = Cells(1, 1).End(xlToRight).Column
For Each c In Range(Cells(1, 1), Cells(1, i))
c.AutoFilter Field:=c.Column, visibledropdown:=False
Next
Application.ScreenUpdating = True
End Sub
Bookmarks