Hi There,

I have an established macro I'd like to modify to exlude some of the data in the raw source. The data is a list of salespeople and their yearly goals.

Right now, the macro copies each salesperson's data to tabs it creates, subtotals and sorts the data in various ways, then compares the salesperson's yearly goals with their current sales.

I'd like to be able to exclude salespeople that no longer work here, but also to include goals for which nobody has made a sale yet. For example: Bob Jones doesn't work here anymore, so I want to exclude his information. Tom Johnson doesn't have any sales yet this year, but I want to show his goal vs. the 0 sales so far. The macro is posted below.

Sub Calculate()

Sheets("Raw").Select
Range("N3").Select
ActiveCell.FormulaR1C1 = "2013"
Range("N4").Select
ActiveCell.FormulaR1C1 = "Sales"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8.25
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("L:L").Select
Selection.Copy
Columns("N:N").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("N3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RC[-8]-RC[-2]"

Dim LR As Long
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("N5").AutoFill Destination:=Range("N5:N" & LR)
Range("A4:A" & LR).EntireRow.Select

Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Key2:=Range("E5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

Sheets("Raw").Select
Sheets("Raw").Copy Before:=Sheets(2)
Sheets("Raw").Select
Range("A1").Select
Sheets("Raw (2)").Select
Sheets("Raw (2)").Name = "Sales by Sales"

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A4:A" & LR).EntireRow.Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 8, 10, _
12, 14), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Sheets("Sales by Sales").Select
Sheets("Sales by Sales").Copy Before:=Sheets(3)
Sheets("Sales by Sales").Select
Range("A1").Select
Sheets("Sales by Sales (2)").Select
Sheets("Sales by Sales (2)").Name = "Sales by Sales by Type"

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A4:A" & LR).EntireRow.Select
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 8, 10, _
12, 14), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Range("A3").Select

ActiveSheet.Outline.ShowLevels RowLevels:=3
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("C:N").Select
Columns("C:N").EntireColumn.AutoFit
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Range("A3").Select
Sheets("Raw").Select
Sheets("Raw").Copy Before:=Sheets(4)
Sheets("Raw (2)").Select
Sheets("Raw (2)").Name = "Sales by Type"

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A4:A" & LR).EntireRow.Select
Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 8, 10, _
12, 14), Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Range("A2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("B:D").Select
Selection.EntireColumn.Hidden = True
Columns("E:N").Select
Columns("E:N").EntireColumn.AutoFit

Range("A3").Select
Sheets("Sales by Sales").Select
Sheets("Sales by Sales").Copy Before:=Sheets(5)
Sheets("Sales by Sales (2)").Select
Sheets("Sales by Sales (2)").Name = "Sales by Sales with Goals"
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Columns("D:M").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("O3").Select
ActiveCell.FormulaR1C1 = "2013"
Range("O4").Select
ActiveCell.FormulaR1C1 = "Goals"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8.25
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("P4").Select
ActiveCell.FormulaR1C1 = "Difference"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8.25
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("N3").Select
Selection.Copy
Range("O3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("O7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],Goals!R2C1:R52C2,2,0)"

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1
Range("O7").AutoFill Destination:=Range("O7:O" & LR)

Columns("N:N").Select
Selection.Copy
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1
Range("A4:A" & LR).Select

Range("P7").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("P7:P" & LR).Select
Selection.FillDown

Columns("O:O").Select
Selection.Copy
Columns("P:P").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Range("N" & Rows.Count).End(xlUp).Offset(0, 1).FormulaR1C1 = "=SUMIF(R2C[-12]:R[-1]C[-12],""*Total*"",R2C:R[-1]C)"

Columns("C:C").EntireColumn.AutoFit
Columns("N:P").EntireColumn.AutoFit

Sheets("Goals").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Sales by Sales with Goals").Select
Range("A1").Select

End Sub