I want to Split & save the data in different workbook

[Amol]

Can anyone please help me in this regards...

I want to split the data in diiferent workbook and save it in new
folder.( every time i split the data the macro should create new
folder for it)

I have data which contains credit and debit details of the clients, i
want to split that data from customer code and save it in foleder with
file name (which contains client code in it )

Please provide me the required VBL query......trying hard for many
days but still not succeded.

 

[Ron de Bruin]

Hi Amol

Try the workbook example
http://www.rondebruin.nl/copy5.htm

 

[Amol]

Hi Amol

Try the workbook examplehttp://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm








- Show quoted text -

Its not working....

i have no idea where the file got saved Plus there is one sheet added
in existing data sheet....

No folder name, no file saved with customer name......

Result = Total confussion

 

[Ron de Bruin]

You must use
http://www.rondebruin.nl/copy5.htm#4)

What have you changed in my example

 

[Amol]

You must usehttp://www.rondebruin.nl/copy5.htm#4)

What have you changed in my example

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm









- Show quoted text -

on which location the folder got saved??? Its just a run macro and add
one sheet with no date just a hearder.....
I think i am not able to explain what i want.........if provide the
exact query for same and not a link

 

[Ron de Bruin]

Please read the information on the site and use the correct example

Check if the information in these lines is correct before you run the macro.

Name of the worksheet with your data table
Set ws1 = Sheets("Sheet1") '<<< Change

Data range: A1 is the top left cell of your filter range and the header of the first column,
and D is the last column in the filter range.
Set rng = ws1.Range("A1" & Rows.Count)

You can change the filter column to another column, my range starts in A so the 1
in my example is the A column(2 = B, 3 =C,...............)
rng.Columns(1).AdvancedFilter _

 

[Amol]

You must usehttp://www.rondebruin.nl/copy5.htm#4)

What have you changed in my example

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm









- Show quoted text -

Hi Robin

File got saved with this macro but the sheet is blank there is no data
in that saved files....i want all filter data in that sheet

everything works fine till creating folder and saving with Customer
code i.e filter range.....but the sheet is blank

Please help

 

[Amol]

Please read the information on the site and use the correct example

Check if the information in these lines is correct before you run the macro.

Name of the worksheet with your data table
Set ws1 = Sheets("Sheet1") '<<< Change

Data range: A1 is the top left cell of your filter range and the header of the first column,
and D is the last column in the filter range.
Set rng = ws1.Range("A1" & Rows.Count)

You can change the filter column to another column, my range starts in A so the 1
in my example is the A column(2 = B, 3 =C,...............)
rng.Columns(1).AdvancedFilter _

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm






- Show quoted text -

thanxs robin......

i am able to split and save the data in seprate folder the only issues
is left now the data which got coppied its only till column D. i want
all all the sheet get coppied as it is


Please help

 

[Ron de Bruin]

Then Change the D to your column in this code line

Set rng = ws1.Range("A1" & Rows.Count)

 

[Amol]

Then Change the D to your column in this code line

Set rng = ws1.Range("A1" & Rows.Count)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm








- Show quoted text -

Thanxs it woking file now.......

Next step is to saving data with file name..... i want file name as"
Daily Credit Report" &" Customer name" instead of VALUE = Customer
Name

& how do i change the saving location of folder.....currently its
taking default location

 

[Ron de Bruin]

If you read the code you see

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath


For the file name

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat

You can change it to this

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Daily Credit Report " _
& cell.Value, ws1.Parent.FileFormat

 

[Amol]

If you read the code you see

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

For the file name

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat

You can change it to this

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Daily Credit Report " _
& cell.Value, ws1.Parent.FileFormat

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm








- Show quoted text -

Hi Robin

Its working perfectaly fine....

1. To make more user friendly, is it possible to make it default to
copy page without defining column range, like u have define as column
D

2. For that need one button as "Open file" to open main data sheet &
function like "Ctrl + K " which open box & ask user to enter the
column from which he want to slipt all data"

& last

3 can macro send the splited & saved files to respective e mail id's
through Outlook 2003

Thanxs in Advance

 

[Ron de Bruin]

Hi Amol

Btw my name Ron

1. To make more user friendly, is it possible to make it default to
copy page without defining column range, like u have define as column D

2. For that need one button as "Open file" to open main data sheet &
function like "Ctrl + K " which open box & ask user to enter the
column from which he want to slipt all data"

Do you want to let the user fill in the last column of the range or the Filter column ?

& last

3 can macro send the splited & saved files to respective e mail id's
through Outlook 2003

There is a lot of mail code on my site
http://www.rondebruin.nl/sendmail.htm
You can use for example the sheet example instead of the workbook example and
then use a macro that select every sheet with a mail address as a seperate workbook.

 

[Ron de Bruin]

Oops

then use a macro that select every sheet with a mail address as a seperate workbook.

then use a macro that copy every sheet with a mail address into a new workbook and mail it to that address.

 

[Amol]

Oops


then use a macro that copy every sheet with a mail address into a new workbook and mail it to that address.

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm









- Show quoted text -

for that

1. i have to create seprate macro which split data in a seprate sheet
instead of seprate workbook then send it to the mail address of
various clients

Series of step that i am thinking on are

a ) need a button on main sheet which ask user to select raw file
which need to b splited in differestn sheet as per the requirements.

b) after the file gets open there must be any cammand that user has to
perform like ( ctrl + k ) which open a text box and ask user to type
column name from which he wants to split whole data as default
(earlier example there was a range of column which we have specify
while writting macro) it shoul copy whole file as default.

2. for sending files to respectives e mail address

a) want one file which contains client code in one column & e mail
address of that particular clinet on another

b) then a seprate macro which copy sheet and send as a seprate work
book to respective mail address

Most important : when the file slipt it should give a sheet name as
'customer code' to indentify the mail macro to recongnise which files
send to whom

Thnaxs in advance

 

[Ron de Bruin]

I see if I have time to create this macro for you

This is not a simple thing

a) want one file which contains client code in one column & e mail
address of that particular clinet on another

How do you want to do this
Do you have this information ?

 

[Amol]

I see if I have time to create this macro for you

This is not a simple thing


How do you want to do this
Do you have this information ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm















- Show quoted text -

Sorry for the delay in reply


& thanxs for your help

i am working in financial sector where we have lots of client which
are defined by their client code. they have collection all over the
country on daily basis and we have to send them a daily MIS.
we have one report in our system which gives consolidate report in a
excel form which we have to send each and every client on thier mail
id's & and all these we are doing manually which take hell of a
time.like filtering the data as per the client code copy that in new
xl file, save it then send a mail.....
I want this thing to be done by macro......and i know its possible
i have one macro in my office which can split the data in different
sheets in a same file.....& the best thing abt that is, it ask user to
inter a column name like (column A, B , C etc) before spliting
it.....but the worse thing is that the macro has password in VBA
query....so i can use that and update more function like sending to
mail id;s and all

If you really thinking to work on it then try to create in a such way
that any lay man can able to use it and update it...and please
consider that the client are increasing regurally and need that option
user friendly to add new client code and mail id's

thanxs in advance

 

[Ron de Bruin]

Where are the mail addresses ?
Also in the data ?

We must know a mail address if we want to send the data

 

[Amol]

Where are the mail addresses ?
Also in the data ?

We must know a mail address if we want to send the data

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm





...

read more »- Hide quoted text -

- Show quoted text -

No...its not in a data

 

[Amol]

...

read more »- Hide quoted text -

- Show quoted text -

i have maintain a seprate xl sheet with client customer client code
with their mail id's...what i am thinking is to connect that file with
splited data file to send mails....

is that possible