Sub Final()
'Application.EnableCancelKey = xlDisabled
Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant
'On Error Resume Next
If Month(Now()) > 9 Then cat = Month(Now()) - 9 Else cat = Month(Now()) + 3
Application.Workbooks.Open ("c:/cust_exp/data/output/q_info.xlsx")
Range("C1").Value = "Select Customer"
Application.Workbooks.Open ("C:/cust_exp/data/output/q_activity.xlsx")
Columns("A").Insert
Columns("E").Copy
Columns("A").PasteSpecial xlPasteAll
Application.Workbooks.Open ("C:/cust_exp/data/output/q_gain_loss.xlsx")
Application.Workbooks.Open ("C:/cust_exp/data/output/q_leads.xlsx")
Application.Workbooks.Open ("C:/cust_exp/data/output/q_opps.xlsx")
Application.Workbooks.Open ("C:/cust_exp/email.xlsx")
Application.Workbooks.Open ("C:/cust_exp/data/output/q_calls.xlsx")

Application.Workbooks.Open ("C:/cust_exp/data/output/q_programs.xlsx")
Application.Workbooks.Open ("C:/cust_exp/data/output/sales_goals.xlsx")

'**********************************************************************
'ranking
Workbooks.Add
salesgoals = ActiveWorkbook.Name
Windows("sales_goals.xlsx").Activate
Cells.Copy
Windows(salesgoals).Activate
ActiveCell.PasteSpecial xlPasteAll
Columns("A:E").Copy
Sheets(2).Select
ActiveCell.PasteSpecial xlPasteAll
Sheets(1).Select
Range("E1").Offset(, cat).Select
ActiveCell.EntireColumn.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=SUM(" & ActiveCell.Offset(0, -cat).Address & ":" & ActiveCell.Offset(0, -1).Address & ")"
ActiveCell.Replace what:="$", replacement:=""
ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(ActiveSheet.UsedRange.Rows.Count - 1, 0))
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("F1").PasteSpecial xlPasteValues
Sheets(1).Select
Range("A1").Select
While Left(ActiveCell, 4) <> "PRxO"
ActiveCell.Offset(0, 1).Select
Wend
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("G1").PasteSpecial xlPasteValues
Sheets(1).Select
ActiveCell.Offset(, cat).Select
ActiveCell.EntireColumn.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=SUM(" & ActiveCell.Offset(0, -cat).Address & ":" & ActiveCell.Offset(0, -1).Address & ")"
ActiveCell.Replace what:="$", replacement:=""
ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(ActiveSheet.UsedRange.Rows.Count - 1, 0))
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("H1").PasteSpecial xlPasteValues
Sheets(1).Select
Range("A1").Select
While Left(ActiveCell, 5) <> "Total"
ActiveCell.Offset(0, 1).Select
Wend
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("I1").PasteSpecial xlPasteValues
Sheets(1).Select
ActiveCell.Offset(, cat).Select
ActiveCell.EntireColumn.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=sum(" & ActiveCell.Offset(0, -cat).Address & ":" & ActiveCell.Offset(0, -1).Address & ")"
ActiveCell.Replace what:="$", replacement:=""
ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(ActiveSheet.UsedRange.Rows.Count - 1, 0))
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("J1").PasteSpecial xlPasteValues
Sheets(1).Select
Range("A1").Select
Rows("1").Find(what:="PRxO1").Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(0, (cat - 1)).Select


ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("K1").PasteSpecial xlPasteValues
Sheets(1).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.EntireColumn.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=sum(" & ActiveCell.Offset(0, -cat).Address & ":" & ActiveCell.Offset(0, -1).Address & ")"
'MsgBox "hi"
ActiveCell.Replace what:="$", replacement:=""
ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(ActiveSheet.UsedRange.Rows.Count - 1, 0))
'MsgBox "hi"
ActiveCell.EntireColumn.Copy
Sheets(2).Select
Range("L1").PasteSpecial xlPasteValues
Range("A2").Select

While ActiveCell.Value <> blank
b = 1
While ActiveCell = ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).Select
b = b + 1
Wend
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
For A = 1 To 3
ActiveCell = ActiveCell.Offset(-1)
ActiveCell.Offset(, 1).Select
Next A
ActiveCell.Offset(, 1).Select
For A = 1 To 8
ActiveCell = "=SUM(" & ActiveCell.Offset(-1, 0).Address & ":" & ActiveCell.Offset(-b).Address & ")"
ActiveCell.Offset(, 1).Select
Next A
ActiveCell.EntireRow.Interior.ColorIndex = 19
'MsgBox ActiveCell.Offset(-1).Row & " " & ActiveCell.Offset(-a).Row
ActiveCell.Offset(1, -12).Select
'MsgBox "hi"
Wend
Cells.Copy
Cells.PasteSpecial xlPasteValues
Range("A2").Select
While ActiveCell <> Empty
If ActiveCell.Interior.ColorIndex <> 19 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Wend

Range("F1").Value = "YTD Sales"
Range("H1").Value = "YTD Pro"
Range("J1").Value = "YTD Goal"
Range("L1").Value = "YTD Pro Goal"
Columns("F").Insert
Columns("H").Insert
Columns("J").Insert
Columns("L").Insert
Columns("N").Insert
Columns("P").Insert
Columns("R").Insert
Range("A2").Select
While ActiveCell.Value <> Empty
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(10, 0)).EntireRow.Delete
Range("A1").Select
Columns("D").Delete
Range("E1").Select
For A = 1 To 4
ActiveCell.Value = 0
Range("A1:Q" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range(ActiveCell.Offset(0, -1).Address), order1:=xlDescending, Header:=xlYes
ActiveCell.Offset(1, 0).Select
While ActiveCell.Offset(0, -4) <> Empty
ActiveCell = ActiveCell.Offset(-1, 0) + 1
ActiveCell.Offset(1, 0).Select
Wend
ActiveCell.Offset(-ActiveSheet.UsedRange.Rows.Count, 2).Select

Next A
Range("M2").Select
For A = 1 To 4
While Range("A" & ActiveCell.Row).Value <> Empty
If ActiveCell.Offset(0, -1) <> 0 Then
ActiveCell = ActiveCell.Offset(0, -9) / ActiveCell.Offset(0, -1)
Else
ActiveCell.Value = 0
End If
ActiveCell.Offset(1, 0).Select
Wend
ActiveCell.Offset(-(ActiveSheet.UsedRange.Rows.Count - 1), 2).Select

Next A
Columns("N").Insert
Columns("Q").Insert
Columns("T").Insert
Range("N1").Select
For A = 1 To 4
ActiveCell.Value = 0
Range("A1:W" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range(ActiveCell.Offset(0, -1).Address), order1:=xlDescending, Header:=xlYes
ActiveCell.Offset(1, 0).Select
While Range("A" & ActiveCell.Row) <> Empty
ActiveCell = ActiveCell.Offset(-1, 0) + 1
ActiveCell.Offset(1, 0).Select
Wend
ActiveCell.Offset(-ActiveSheet.UsedRange.Rows.Count, 3).Select

Next A
acm_ct = ActiveSheet.UsedRange.Rows.Count
'***********************************************************************

Application.Workbooks.Open ("c:/cust_exp/data/output/q_acm.xlsx")
MkDir "c:\cust_exp\" & Format(Date, "YYYY-MM-DD")
Range("A2").Select
meow = 0
While ActiveCell.Value <> Empty
meow = meow + 1
If meow = 90 Then MsgBox "do the send thing now."
acm = ActiveCell.Offset(0, 1).Value
acmnum = ActiveCell
acmst = ActiveCell.Offset(0, 2).Value
Application.Workbooks.Open ("c:/cust_exp/template2.xlsx")
'MsgBox "hi"
ActiveWorkbook.SaveAs Filename:="c:\cust_exp\" & Format(Date, "YYYY-MM-DD") & "/" & acm & " " & acmnum & " - " & Format(Date, "YYYY-MM-DD") & ".xlsx", FileFormat:=51
'MsgBox "hi"
acm_sheet = ActiveWorkbook.Name

Sheets("activity").Activate
Windows("q_activity.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_activity.xlsx").Activate
Range("B1").Select
On Error Resume Next
Columns("B").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo activity_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct)).EntireRow.Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteValues


activity_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("gain-loss").Activate
Windows("q_gain_loss.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_gain_loss.xlsx").Activate
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo gl_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 108)).Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll
gl_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("info").Activate
Windows("q_info.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_info.xlsx").Activate
Range("A1").Select
'On Error Resume Next
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo info_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct)).EntireRow.Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll

Range("B2").Select
While ActiveCell <> Empty
ActiveCell.Offset(1, 0).Select
pika = ActiveCell.Row
Wend
Range("C2:C" & pika).Select

ActiveWorkbook.Names.Add Name:="cat", RefersTo:=Selection

info_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("leads").Activate
Windows("q_leads.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_leads.xlsx").Activate
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo leads_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 108)).Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll
leads_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("opps").Activate
Windows("q_opps.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_opps.xlsx").Activate
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo opps_exit
End If

ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 108)).Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll
opps_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("programs").Activate
Windows("q_programs.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("q_programs.xlsx").Activate
Range("A1").Select
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo prog_exit
End If

ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 108)).Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll
prog_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate


Sheets("sales-goals").Activate
Windows("sales_goals.xlsx").Activate
Rows("1").Copy
Windows(acm_sheet).Activate
Rows("1").PasteSpecial xlPasteAll
Windows("sales_goals.xlsx").Activate
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo sales_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 108)).Copy
Windows(acm_sheet).Activate
Range("A2").PasteSpecial xlPasteAll
Range("E" & ActiveSheet.UsedRange.Rows.Count + 1).Value = "=SUM(E2:E" & ActiveSheet.UsedRange.Rows.Count & ")"
Range("E" & ActiveSheet.UsedRange.Rows.Count).AutoFill Destination:=Range("E" & ActiveSheet.UsedRange.Rows.Count & ":DE" & ActiveSheet.UsedRange.Rows.Count)
Sheets("Performance").Activate
Range("B6").Activate
ActiveCell = Month(Now) & "/1/" & Year(Now)
ActiveCell.Offset(0, 1).Select
For b = 1 To 12
ActiveCell = Month(ActiveCell.Offset(0, -1) - 1) & "/1/" & Year(ActiveCell.Offset(0, -1) - 1)
ActiveCell.Offset(0, 1).Select
Next b
Range("C8").Select
ActiveCell = Month(ActiveCell.Offset(-2, 11) - 1) & "/1/" & Year(ActiveCell.Offset(-2, 11) - 1)
ActiveCell.Offset(0, 1).Select
For b = 1 To 11
ActiveCell = Month(ActiveCell.Offset(0, -1) - 1) & "/1/" & Year(ActiveCell.Offset(0, -1) - 1)
ActiveCell.Offset(0, 1).Select
Next b
Range("C4").Select
For b = 1 To 12
ActiveCell = MonthName(Month(ActiveCell.Offset(2, 0)))
ActiveCell.Offset(0, 1).Select
Next b

Range("B7").Select
Sheets("sales-goals").Select
Range("E" & ActiveSheet.UsedRange.Rows.Count & ":Q" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
ActiveCell.PasteSpecial xlPasteValues
Sheets("sales-goals").Select
Range("AD" & ActiveSheet.UsedRange.Rows.Count & ":AP" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
Range("B45").PasteSpecial xlPasteValues
Sheets("sales-goals").Select
Range("BC" & ActiveSheet.UsedRange.Rows.Count & ":BO" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
Range("B26").PasteSpecial xlPasteValues
Sheets("sales-goals").Select
Range("R" & ActiveSheet.UsedRange.Rows.Count & ":AC" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
Range("C9").PasteSpecial xlPasteValues
Sheets("sales-goals").Select
Range("BP" & ActiveSheet.UsedRange.Rows.Count & ":CA" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
Range("C28").PasteSpecial xlPasteValues
Sheets("sales-goals").Select
Range("AQ" & ActiveSheet.UsedRange.Rows.Count & ":BB" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Performance").Select
Range("c47").PasteSpecial xlPasteValues
Rows("6").Copy
Rows("25").PasteSpecial xlPasteAll
Rows("44").PasteSpecial xlPasteAll
Rows("8").Copy
Rows("27").PasteSpecial xlPasteAll
Rows("46").PasteSpecial xlPasteAll
sales_exit:
Windows(acm_sheet).Activate
Sheets("Accounts").Select
Range("C27").Select
ActiveCell = Month(Now) & "/1/" & Year(Now)
ActiveCell.Offset(0, 1).Select
For b = 1 To 11
ActiveCell = Month(ActiveCell.Offset(0, -1) - 1) & "/1/" & Year(ActiveCell.Offset(0, -1) - 1)
ActiveCell.Offset(0, 1).Select
Next b
Range("C12").Activate
ActiveCell = Month(Now) & "/1/" & Year(Now)
ActiveCell.Offset(0, 1).Select
For b = 1 To 11
ActiveCell = Month(ActiveCell.Offset(0, -1) - 1) & "/1/" & Year(ActiveCell.Offset(0, -1) - 1)
ActiveCell.Offset(0, 1).Select
Next b
Sheets("Goal Overview").Select
Range("C21").Value = "='sales-goals'!CB" & Sheets("sales-goals").UsedRange.Rows.Count
Range("C22").Value = "='sales-goals'!E" & Sheets("sales-goals").UsedRange.Rows.Count
Range("I21").Value = "='sales-goals'!CN" & Sheets("sales-goals").UsedRange.Rows.Count
Range("I22").Value = "='sales-goals'!BC" & Sheets("sales-goals").UsedRange.Rows.Count
Range("E21").Value = "=SUM('sales-goals'!CB" & Sheets("sales-goals").UsedRange.Rows.Count & ":OFFSET('sales-goals'!CB" & Sheets("sales-goals").UsedRange.Rows.Count & ",,(" & cat & "-1)))"
Range("E22").Value = "=SUM('sales-goals'!E" & Sheets("sales-goals").UsedRange.Rows.Count & ":OFFSET('sales-goals'!E" & Sheets("sales-goals").UsedRange.Rows.Count & ",,(" & cat & "-1)))"
Range("K22").Value = "=SUM('sales-goals'!BC" & Sheets("sales-goals").UsedRange.Rows.Count & ":OFFSET('sales-goals'!BC" & Sheets("sales-goals").UsedRange.Rows.Count & ",,(" & cat & "-1)))"
Range("K21").Value = "=SUM('sales-goals'!CN" & Sheets("sales-goals").UsedRange.Rows.Count & ":OFFSET('sales-goals'!CN" & Sheets("sales-goals").UsedRange.Rows.Count & ",,(" & cat & "-1)))"
'MsgBox "hi"
Sheets("sales-goals").Select
Range("CZ2").Select
ActiveCell.Value = "=E2/offset(CB2,," & (cat - 1) & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=bc2/offset(cn2,," & (cat - 1) & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(E2:offset(E2,," & cat & "-1)))/sum(CB2:offset(cb2,," & cat & "-1)))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(BC2:offset(BC2,,(" & cat & "-1)))/sum(CN2:offset(CN2,,(" & cat & "-1)))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(E2:offset(E2,,(" & cat & "-1)))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(BC2:offset(BC2,,(" & cat & "-1)))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(cb2:offset(cb2,,(" & cat & "-1)))"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUM(cn2:offset(cn2,,(" & cat & "-1)))"
Range("CZ2:DG2").AutoFill Destination:=Range("CZ2:DG" & (ActiveSheet.UsedRange.Rows.Count - 1))
Range("A1:DG" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range("DD1"), order1:=xlDescending, Header:=xlYes

Rows("1:11").Copy
Sheets("temp").Select
Range("A1").PasteSpecial xlPasteValues
Range("d2:d11").Copy
Sheets("Top-Bottom 10").Select
Range("C10").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("dd2:dd11").Copy
Sheets("Top-Bottom 10").Select
Range("H10").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("df2:df11").Copy
Sheets("Top-Bottom 10").Select
Range("j10").PasteSpecial xlPasteValues
Range("H9").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Range("J9").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Sheets("temp").Select
Cells.ClearContents
Sheets("sales-goals").Select
Range("A1:DG" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range("DE1"), order1:=xlDescending, Header:=xlYes

Rows("1:11").Copy
Sheets("temp").Select


Range("A1").PasteSpecial xlPasteValues
Range("d2:d11").Copy
Sheets("Top-Bottom 10").Select
Range("C25").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("dd2:dd11").Copy
Sheets("Top-Bottom 10").Select
Range("H25").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("df2:df11").Copy
Sheets("Top-Bottom 10").Select
Range("j25").PasteSpecial xlPasteValues
Range("H24").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Range("J24").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Sheets("temp").Select
Cells.ClearContents
Sheets("sales-goals").Select
Range("A1:DG" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range("DD1"), order1:=xlAscending, Header:=xlYes
'MsgBox "hi"
If Month(Now()) > 9 Then cat = Month(Now()) - 9 Else cat = Month(Now()) + 3
bigcat = cat * 5000
Range("DD2").Select
While ActiveCell < bigcat
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row > ActiveSheet.UsedRange.Rows.Count Then
Sheets("Top-Bottom 10").Select
Range("C55").Value = "No Data Available"
GoTo mew2
End If

Wend
Range(ActiveCell, ActiveCell.Offset(10, 0)).EntireRow.Copy
Sheets("temp").Select
Range("A1").PasteSpecial xlPasteValues
Range("d1:d10").Copy
Sheets("Top-Bottom 10").Select
Range("C40").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("dd1:dd10").Copy
Sheets("Top-Bottom 10").Select
Range("H40").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("df1:df10").Copy
Sheets("Top-Bottom 10").Select
Range("j40").PasteSpecial xlPasteValues
mew2:
Range("H39").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Range("J39").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Sheets("temp").Select
Cells.ClearContents
Sheets("sales-goals").Select
Range("A1:DG" & ActiveSheet.UsedRange.Rows.Count - 1).Sort key1:=Range("DE1"), order1:=xlAscending, Header:=xlYes
'MsgBox "hi"
Range("DE2").Select

While ActiveCell < bigcat
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row > ActiveSheet.UsedRange.Rows.Count Then
Sheets("Top-Bottom 10").Select
Range("C55").Value = "No Data Available"
GoTo mew
End If
Wend

Range(ActiveCell, ActiveCell.Offset(10, 0)).EntireRow.Copy
Sheets("temp").Select
Range("A1").PasteSpecial xlPasteValues
Range("d1:d10").Copy
Sheets("Top-Bottom 10").Select
Range("C55").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("dE1:de10").Copy
Sheets("Top-Bottom 10").Select
Range("H55").PasteSpecial xlPasteValues
Sheets("temp").Select
Range("dg1:dg10").Copy
Sheets("Top-Bottom 10").Select
Range("j55").PasteSpecial xlPasteValues
mew:
Range("H54").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Range("J54").Select
A = 1
For A = 1 To 12
Range(ActiveCell, ActiveCell.Offset(0, 1)).MergeCells = True
ActiveCell.Offset(1, 0).Select
Next A
Sheets("gain-loss").Select
Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).EntireRow.Copy
Sheets("Gains-Losses").Select
Range("A20").PasteSpecial xlPasteValues
Range("A20:N20").Select
With Selection
.Interior.ColorIndex = 49
.Font.ColorIndex = 2
.Font.Bold = True
End With
Range("A20:N" & ActiveSheet.UsedRange.Rows.Count + 2).Borders.ColorIndex = 1
Columns.AutoFit
Range("E21").Select
gain = 0
loss = 0
While ActiveCell.Value <> Empty
If ActiveCell.Value = "Gain" Then gain = gain + ActiveCell.Offset(0, 2).Value
If ActiveCell.Value = "Loss" Then loss = loss + ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(1, 0).Select
Wend
Range("H3").Value = gain
Range("H4").Value = loss
Sheets("opps").Select
Range("A1:K" & ActiveSheet.UsedRange.Rows.Count).Copy
Sheets("Opportunities").Select
Range("A1").PasteSpecial xlPasteValues
Range("A1:K1").Select
With Selection
.Interior.ColorIndex = 49
.Font.ColorIndex = 2
.Font.Bold = True
End With
Columns("F").NumberFormat = "mm/dd/yy;@"
Columns("H:I").NumberFormat = "mm/dd/yy;@"
Range("A1:K" & ActiveSheet.UsedRange.Rows.Count).Borders.ColorIndex = 1

Sheets("Goal Overview").Select
Windows(salesgoals).Activate
Columns("A").Find(what:=acmnum).Select

Range("E" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("C27").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("G" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("E27").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("I" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("I27").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("K" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("K27").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("N" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("C28").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("Q" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("E28").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("T" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("I28").Select
ActiveCell.PasteSpecial
Windows(salesgoals).Activate
Range("W" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("K28").Select
ActiveCell.PasteSpecial


Range("E" & ActiveCell.Row).Copy
Windows(acm_sheet).Activate
Range("C32") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("G" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("E32") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("I" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("I32") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("K" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("K32") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("N" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("C33") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("Q" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("E33") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("T" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("I33") = (acm_ct - r) / acm_ct

Windows(salesgoals).Activate
r = Range("W" & ActiveCell.Row)
Windows(acm_sheet).Activate
Range("K33") = (acm_ct - r) / acm_ct

Windows("q_calls.xlsx").Activate
Range("A1").Select
Columns("A").Find(what:=acmnum).Select
If ActiveCell.Value <> acmnum Then
GoTo call_exit
End If
ct = 0
While ActiveCell = ActiveCell.Offset(1, 0)
ct = ct + 1
ActiveCell.Offset(1, 0).Select
Wend
Range(ActiveCell, ActiveCell.Offset(-ct, 6)).Copy
Windows(acm_sheet).Activate
Sheets("calls").Activate

Range("A1").PasteSpecial xlPasteAll
call_exit:
Application.CutCopyMode = False
Windows(acm_sheet).Activate

For A = 1 To 6
Sheets(A).Select
Columns.AutoFit
Range("A1").Select
Next A
For A = 7 To 14
Sheets(A).Visible = xlVeryHidden
Next A
Sheets(1).Select
ActiveWorkbook.Save
Windows("email.xlsx").Activate
Columns("A").Find(what:=acmst).Select
ActiveCell.Offset(0, 1).Select
acmemail = ActiveCell.Value
Windows(acm_sheet).Activate
Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)
Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)
OlMail.Recipients.Add acmemail
OlMail.Subject = "Sales Analytics Tool - " & acm
OlMail.body = "Attached is the Sales Analytics tool for your review."
OlMail.Attachments.Add ActiveWorkbook.FullName
OlMail.Display 'OlMail.Send
ActiveWorkbook.Close
Windows("q_acm.xlsx").Activate
'MsgBox acm

ActiveCell.Offset(1, 0).Select
'MsgBox ActiveCell
Wend
Range("A1").Select



End Sub