$RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",56,,,,False)

;*************************************************************************
;  Script Name:     FindManagers.kix
;  Author:          Wim Rotty
;  Date:            26/09/2007
;  Update by:       1984 /CY
;  Description:     Read, sort and filter data from an Excel book, 
;                   then create new book And Copy/paste data to it.
;*************************************************************************

;Script Options
If Not @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
    $RC = SetOption("WrapAtEOL", "On")
EndIf

;Declare variables
Dim $MyTable
Dim $objExcel
Dim $xlDown, $xlToLeft, $xlToRight, $xlUp
Dim $Row, $i, $Manager, $Managers[0], $j

;Initialize variables
$MyTable = "C:\Test\book1.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR
    Exit @ERROR
EndIf
$xlDown = -4121 
$xlToLeft = -4159 
$xlToRight = -4161 
$xlUp = -4162 
$j = 0

;Do not show alerts like messages about overwriting files
$objExcel.DisplayAlerts = False
;Show Excel, not really needed if you quit at the end
;$objExcel.Visible = -1
;Open file
$RC = $objExcel.Workbooks.Open($MyTable)
;Add filter
$RC = $objExcel.Selection.Autofilter
;Select cell at bottom right of data range
$RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select
;Get cell rownumber
$Row = $objExcel.ActiveCell.Row

;Recurse cells up to cell 2 and gather all the Manager names in an array
For $i = $row to 2 step -1
    ;Get the text (manager name) in the cell (Column "L")
    $Manager = $objExcel.Range("L"+$i).Text
    ;Is the manager name NOT in the array?
    If AScan($Managers, $Manager) = -1
        ;If not, add location to array and add it to new location
        ReDim Preserve $Managers[$j]
        $Managers[$j] = $Manager
        $j = $j + 1
    EndIf
Next

;Recurse the Managers array, Filter for each manager name
For Each $Manager in $Managers
    ;Add AutoFilter for manager on column 12 (= L)
    $RC = $objExcel.Selection.AutoFilter(12, $Manager)
	 ;Select all data in range Column A to column P
	 $RC = $objExcel.Range($objExcel.Selection, $objExcel.Columns("A:P")).Select
    ;Copy the selection to the clipboard
    $RC = $objExcel.Selection.Copy
    ;Add a new workbook
    $RC = $objExcel.Workbooks.Add
    ;Paste the data from the clipboard
    $RC = $objExcel.ActiveSheet.Paste
	 ;Select Column E to P
	 $RC = $objExcel.Columns("E:P").Select
	 ;Hide column E to P
	 $objExcel.Selection.EntireColumn.Hidden = True 
	 ;Autofit visible columns (A to D)  
	 $RC = $objExcel.Columns("A:D").EntireColumn.AutoFit
    ;Save the workbook with as name the manager
    $RC = $objExcel.ActiveWorkbook.SaveAs(@SCRIPTDIR+"\"+$Manager+".xls",,,,,False)
    ;The next active sheet is the original file
    $RC = $objExcel.ActiveWindow.ActivateNext
Next

;Close Excel
$RC = $objExcel.Application.Quit

;Personal UDF Section