SHARE
TWEET

Filtering Macro - BWJ

coffeeandbooks Sep 5th, 2019 97 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub BZW()
  2. '
  3. ' BZW Macro
  4. '
  5.  
  6. '
  7.    ActiveWindow.SmallScroll Down:=-15
  8.     Columns("C:C").Select
  9.     Selection.Delete Shift:=xlToLeft
  10.     Columns("B:B").Select
  11.     Selection.Delete Shift:=xlToLeft
  12.     Columns("F:F").Select
  13.     Selection.Delete Shift:=xlToLeft
  14.     Columns("I:I").Select
  15.     ActiveWindow.ScrollColumn = 2
  16.     ActiveWindow.ScrollColumn = 3
  17.     ActiveWindow.ScrollColumn = 4
  18.     ActiveWindow.ScrollColumn = 5
  19.     ActiveWindow.ScrollColumn = 6
  20.     Columns("T:T").Select
  21.     Selection.Delete Shift:=xlToLeft
  22.     ActiveWindow.ScrollColumn = 5
  23.     ActiveWindow.ScrollColumn = 4
  24.     ActiveWindow.ScrollColumn = 3
  25.     ActiveWindow.ScrollColumn = 2
  26.     Columns("E:E").EntireColumn.AutoFit
  27.     Columns("D:D").EntireColumn.AutoFit
  28.     ActiveWindow.ScrollColumn = 1
  29.     ActiveWindow.ScrollColumn = 2
  30.     Columns("J:J").Select
  31.     Selection.Delete Shift:=xlToLeft
  32.     Columns("J:O").Select
  33.     Columns("J:O").EntireColumn.AutoFit
  34.     Columns("O:O").Select
  35.     Selection.Delete Shift:=xlToLeft
  36.     Range("M7").Select
  37.     Columns("O:O").EntireColumn.AutoFit
  38.     Columns("P:P").EntireColumn.AutoFit
  39.     Columns("Q:Q").Select
  40.     Selection.Delete Shift:=xlToLeft
  41.     ActiveWindow.ScrollColumn = 1
  42.     Columns("B:B").EntireColumn.AutoFit
  43.     Columns("B:B").Select
  44.     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  45.     Selection.ColumnWidth = 29
  46.     Range("B1").Select
  47.     Columns("J:J").EntireColumn.AutoFit
  48.     ActiveCell.FormulaR1C1 = "NM"
  49.     Range("B2").Select
  50.     ActiveCell.FormulaR1C1 = "=IF(OR(RC[10]=0,RC[10]=RC[13]),""Recruit"",""Hold"")"
  51.     Range("B2").Select
  52.     Selection.AutoFill Destination:=Range("B2:B130")
  53.     Range("B2:B130").Select
  54.     Columns("C:C").EntireColumn.AutoFit
  55.     Columns("D:D").Select
  56.     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  57.     Range("B1").Select
  58.     ActiveCell.FormulaR1C1 = "Focus"
  59.     Range("D1").Select
  60.     ActiveCell.FormulaR1C1 = "Client"
  61.     Range("D2").Select
  62.     ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[4],""//"",RC[5],"", "")"
  63.     Range("D2").Select
  64.     ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[4],""//"",RC[5],"", "",RC[6])"
  65.     Range("D2").Select
  66.     Columns("D:D").EntireColumn.AutoFit
  67.     Range("D2").Select
  68.     Selection.AutoFill Destination:=Range("D2:D130")
  69.     Range("D2:D130").Select
  70.     Columns("H:J").Select
  71.     Range("J1").Activate
  72.     Selection.EntireColumn.Hidden = True
  73.     Columns("K:K").Select
  74.     Selection.Delete Shift:=xlToLeft
  75.     Range("K14").Select
  76.     ActiveWindow.SmallScroll Down:=-15
  77.     Columns("K:O").Select
  78.     Selection.EntireColumn.Hidden = True
  79.     Range("R17").Select
  80.     ActiveWindow.SmallScroll Down:=-15
  81.     Range("B1").Select
  82.     Selection.AutoFilter
  83.     ActiveSheet.Range("$A$1:$Q$130").AutoFilter Field:=2, Criteria1:="Recruit"
  84.     ActiveWindow.SmallScroll Down:=-15
  85.     Range("D1").Select
  86.     ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
  87.     ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
  88.         Range("C2:C130"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
  89.         :=xlSortNormal
  90.     ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
  91.         Range("D2:D130"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
  92.         :=xlSortNormal
  93.     ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
  94.         Range("F2:F130"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
  95.         :=xlSortNormal
  96.     With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
  97.         .Header = xlYes
  98.         .MatchCase = False
  99.         .Orientation = xlTopToBottom
  100.         .SortMethod = xlPinYin
  101.         .Apply
  102.     End With
  103. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top