A part of the code is made using the macro recorder
I will try to give an example
  • Start MS Excel
  • Start the Macro Recorder via Tools --> Macro --> Record new macro...
  • Do a File --> Save As and give the file a name
  • Stop the macro recorder
  • Go to Tools --> Macro --> Macros
  • Start editing the recorded macro
  • Now you have to be a littlebit inventive and decide what you really need in this code and how you can paste this in your KiX script. Anyway, you learned you will need ActiveWorkbook.SaveAs

Maybe you also want to get rid of annoying messages telling that you will overwrite files. Google is your friend
http://www.google.be/search?hl=nl&q=ActiveWorkbook.SaveAs+site%3Amicrosoft.com&meta=
First hit
http://support.microsoft.com/kb/213641
Silver platter with some explanation
;*************************************************************************
;  Script Name:     FindManagers.kix
;  Author:         Wim Rotty
;  Date:             26/09/2007
;  Description:     Copy and paste data from one Excel book to other books
;*************************************************************************
;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\Tabel.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR
    Exit @ERROR
EndIf
$xlDown = -4121
$xlToLeft = -4159
$xlToRight = -4161
$xlUp = -4162
$j = 0

;Code
;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
    $Manager = $objExcel.Range("D"+$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
;Select all data in range
$RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select
;Recurse the Managers array
For Each $Manager in $Managers
    ;Filter for each manager name
    $RC = $objExcel.Selection.AutoFilter(4, $Manager)
    ;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 the D column with the manager name
    $RC = $objExcel.Columns("D:D").Select
    ;Clear all in this column
    $RC = $objExcel.Selection.Clear
    ;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

;UDF Section

Edited by Witto (2007-09-28 07:33 PM)
Edit Reason: Solved SaveAs not overwriting and added deletion of column D in new workbooks