Macro code
 

I need some help in developing this macro so it selects certain criteria. I
believe it needs a variable, but I'm not quite sure on how to do it.

What the macro does now is that it take a worksheet and cleans up the data
that isn't need and then sort different columns. (all the codes is here)

What I would like it to do is, select all of "corporate services" and move
it to another worksheet. and then do the next business unit, etc. What I
have it doing now is selecting the rows that "corporate services" is in and
moving it. It doesn't work if rows are added or deleted. Is there some code
that could be written to do this? Help! Thanks

Sub Test()

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Cells.Find(What:="User Name", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("500:500").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Cells.Find(What:="Timestamp", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("500:500").Select
Selection.Delete Shift:=xlUp
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.ColumnWidth = 50
Columns("C:F").Select
Selection.ColumnWidth = 15
ActiveWindow.SmallScroll ToRight:=6
Columns("N:N").Select
Selection.ColumnWidth = 50
Columns("O:Q").Select
Selection.ColumnWidth = 15
Columns("R:AB").Select
Selection.ColumnWidth = 25
ActiveWindow.ScrollColumn = 1
Cells.Select
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows("2:95").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.RowHeight = 25
With Selection
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Rows("318:401").Select
Selection.Delete Shift:=xlUp
End With
Cells.Select
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll ToRight:=10
Cells.Select
Range("K1").Activate
Selection.Sort Key1:=Range("P2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2").Select
ActiveWindow.SmallScroll ToRight:=10
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-9
Columns("P:Q").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""10%Late - 10%Ahead""", Formula2:="=""10%Under-10%Over"""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""11%-20% Late""", Formula2:="=""11%-20% Over"""
Selection.FormatConditions(2).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""More than 20% Late""", Formula2:="=""More than 20%
Over"""
Selection.FormatConditions(3).Interior.ColorIndex = 3
Cells.Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Grid Data").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.Insert Shift:=xlDown
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=14
Rows("2:218").Select
Range("O2").Activate
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Corporate Services"
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=168
Rows("186:186").Select
Range("O186").Activate
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Range("A2").Select
ActiveWindow.SmallScroll Down:=174
Range("A186").Select
ActiveWindow.SmallScroll ToRight:=14
Rows("186:218").Select
Range("O186").Activate
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Corporate Systems"
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=30
Range("T219").Select
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=207
Range("N219").Select
Rows("219:247").Select
Range("N219").Activate
Selection.Copy
Sheets("Sheet3").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Customer Services"
Sheets("Grid Data").Select
Application.CutCopyMode = False
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=267
ActiveWindow.SmallScroll Down:=-21
Rows("248:255").Select
Range("P248").Activate
Selection.Copy
Sheets("Sheet4").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Business Unit 2"
Sheets("Grid Data").Select
Application.CutCopyMode = False
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet5").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=252
Rows("256:286").Select
Range("P256").Activate
Selection.Copy
Sheets("Sheet5").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Business Unit 3"
Sheets("Grid Data").Select
Application.CutCopyMode = False
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet6").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=276
Rows("287:316").Select
Selection.Copy
Sheets("Sheet6").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Business Unit 3"
Sheets("Grid Data").Select
Application.CutCopyMode = False
Range("A1").Select
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet7").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=300
Rows("317:317").Select
Selection.Copy
Sheets("Sheet7").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "Business Unit 4"
Range("D44").Select
Sheets("Grid Data").Select
Application.CutCopyMode = False
Range("A1").Select
Sheets.Add
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "IS"
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("IS").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Rows("2:317").Select
Selection.Copy
Sheets("IS").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Range("A6").Select
Columns("V:AB").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets.Add
Sheets("Grid Data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Sheet9").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Copy
Sheets("Sheet9").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("Grid Data").Select
Application.CutCopyMode = False
Columns("D:D").Select
Selection.Copy
Sheets("Sheet9").Select
Columns("E:E").Select
ActiveSheet.Paste
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Grid Data").Select
Columns("F:F").Select
Selection.Copy
Sheets("Sheet9").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Sheets("Grid Data").Select
Columns("K:N").Select
Selection.Copy
Sheets("Sheet9").Select
Columns("G:G").Select
ActiveSheet.Paste
Columns("D:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Grid Data").Select
Columns("P:P").Select
Selection.Copy
Sheets("Sheet9").Select
Columns("I:I").Select
ActiveSheet.Paste
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:T").Select
Selection.Delete Shift:=xlToLeft
Range("K10").Select
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "CIO"
Sheets("Grid Data").Select
Sheets.Add
Sheets("Grid Data").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet10").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Range("B1").Select
Selection.Copy
Sheets("Sheet10").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("Grid Data").Select
Range("F1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet10").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C1").Select
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Copy
Sheets("Sheet10").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Grid Data").Select
Range("B2:B317").Select
Selection.Copy
Sheets("Sheet10").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Grid Data").Select
Application.CutCopyMode = False
Columns("F:F").Select
Selection.Copy
Sheets("Sheet10").Select
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("D15").Select
ActiveWindow.SmallScroll Down:=-15
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "Financial"
Sheets("Grid Data").Select
End Sub