Ron de Bruin has an addin that does the separation into different worksheets
(within the same workbook, I think).

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

He also has an addin for integrating emails:
http://www.rondebruin.nl/mail/add-in.htm

If you want more control, Debra Dalgleish has some sample code:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron has lots of mailing tips/code samples he
http://www.rondebruin.nl/sendmail.htm

Amol wrote:

D/all

I have a data which contain the details of credit & debit of
clients....each customer has its own customer code No. in one column
and name on other, I want to create a macro which can split the data
in different workbook & save it with the customer name mentioned in
data file & further if possible send to the e mail id's

Thanks & Regards,
Amol

--

Dave Peterson