|
I want to Split & save the data in different workbook
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. |
I want to Split & save the data in different workbook
Hi Amol
Try the workbook example http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... 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. |
I want to Split & save the data in different workbook
On Jul 20, 10:57 pm, "Ron de Bruin" wrote:
Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
You must use
http://www.rondebruin.nl/copy5.htm#4) What have you changed in my example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 20, 11:16 pm, "Ron de Bruin" wrote:
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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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:D" & 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 Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 20, 11:16 pm, "Ron de Bruin" wrote:
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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 20, 11:48 pm, "Ron de Bruin" wrote:
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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
Then Change the D to your column in this code line
Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 21, 12:28 am, "Ron de Bruin" wrote:
Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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 Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 21, 1:27 am, "Ron de Bruin" wrote:
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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 22, 1:56 am, "Ron de Bruin" wrote:
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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 23, 2:18 pm, "Ron de Bruin" wrote:
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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
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 Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 |
I want to Split & save the data in different workbook
On Jul 26, 9:27 pm, "Ron de Bruin" wrote:
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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text ... read more »- Hide quoted text - - Show quoted text - No...its not in a data |
I want to Split & save the data in different workbook
On Jul 26, 9:32 pm, Amol wrote:
On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips..htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 ... 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 |
I want to Split & save the data in different workbook
Yes it is possible but if you are not the only user you must add a lot of error checking
If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 ... 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 |
I want to Split & save the data in different workbook
BTW: please post on top of this thread and not below.
You must scroll down now every time to read your reply -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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 ... 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 |
I want to Split & save the data in different workbook
On Jul 27, 12:12 am, "Ron de Bruin" wrote:
Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- ... read more »- Hide quoted text - - Show quoted text - Gr8....this can b done....... Column A is always for customer code and same we can keep for permanant.....what is the next step.... How can i send mail through outlook....is there any macro need to be define in outlook. I already prepare a file of mail address of client according to their client code. and what if the report if not found for any given date for particular client..what msg does outlook send for that |
I want to Split & save the data in different workbook
ok.....sir
but do we need to create two seprate macro for all mentioned function or it may include in a single macro... i think two seprate macro will be better.... need ur guidance On Jul 27, 12:19 am, "Ron de Bruin" wrote: BTW: please post on top of this thread and not below. You must scroll down now every time to read your reply -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in ... Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - Show quoted text - Hi Robin Its working perfectaly fine.... 1. To make more user friendly, is it possible to ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
You have the code to split your data so you have seperate sheets now named after the customer code.
Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- ... read more »- Hide quoted text - - Show quoted text - Gr8....this can b done....... Column A is always for customer code and same we can keep for permanant.....what is the next step.... How can i send mail through outlook....is there any macro need to be define in outlook. I already prepare a file of mail address of client according to their client code. and what if the report if not found for any given date for particular client..what msg does outlook send for that |
I want to Split & save the data in different workbook
Its showin compile error on this line MailAdress = Application.WorksheetFunction.VLookup(sh.Name,Shee ts("LookupTable").Range("A1:B3"), 2, False) Pls tell me if i understood right 1) I have a file which has a different data in each sheet and each sheet has name of that particular client code 2) now to send this sheet as a file i have to create a table (with the instruction given by you )in sheet which is a additional sheet in a same file Then i have to run macro Is it right ?? Or u want me to save a new xl file for client code and mail address On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in messagenews:1184955064.213797.135420@x35g2000prf.. googlegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin..nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
I think the line is red in the VBA editor
It is one code line Amol I think the newsgroup split it in two -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... Its showin compile error on this line MailAdress = Application.WorksheetFunction.VLookup(sh.Name,Shee ts("LookupTable").Range("A1:B3"), 2, False) Pls tell me if i understood right 1) I have a file which has a different data in each sheet and each sheet has name of that particular client code 2) now to send this sheet as a file i have to create a table (with the instruction given by you )in sheet which is a additional sheet in a same file Then i have to run macro Is it right ?? Or u want me to save a new xl file for client code and mail address On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in messagenews:1184955064.213797.135420@x35g2000prf.. googlegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin..nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- ... read more »- Hide quoted text - - Show quoted text - Gr8....this can b done....... Column A is always for customer code and same we can keep for permanant.....what is the next step.... How can i send mail through outlook....is there any macro need to be define in outlook. I already prepare a file of mail address of client according to their client code. and what if the report if not found for any given date for particular client..what msg does outlook send for that |
I want to Split & save the data in different workbook
If nothing happens then this is not correct I think in your test workbook
Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 20, 10:57 pm, "Ron de Bruin" wrote: Hi Amol Try the workbook examplehttp://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... 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.- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- Hide quoted text - - 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- ... read more »- Hide quoted text - - Show quoted text - Gr8....this can b done....... Column A is always for customer code and same we can keep for permanant.....what is the next step.... How can i send mail through outlook....is there any macro need to be define in outlook. I already prepare a file of mail address of client according to their client code. and what if the report if not found for any given date for particular client..what msg does outlook send for that |
I want to Split & save the data in different workbook
Send me the workbook with the data and your sheet with mail addresses private.
Be sure that the code you have try is in that workbook. I will look at it tomorrow then for you after work -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... I have alredy added one sheet in the workbook where i have other sheets of customer details like first sheet is "lookup table" with client code in A column and email address in B Second sheet is of one of client code same for second and third I am running macro in look up table sheet and nothing happens No error No Result i have also given name for column A as "customer code" and for B "mail address" What else i have to do......... Can u mail me the macro enabled macro on Thanxs On Jul 29, 3:20 am, "Ron de Bruin" wrote: If nothing happens then this is not correct I think in your test workbook Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 20, 11:48 pm, "Ron de Bruin" wrote: 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:D" & 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 "Amol" wrote in oglegroups.com... On Jul 20, 11:16 pm, "Ron de ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
But what is the problem...why its not working
what i need to do for that? & Where i can send the file? i am using dummy data its just a number and my e mail address only On Jul 30, 12:34 am, "Ron de Bruin" wrote: Send me the workbook with the data and your sheet with mail addresses private. Be sure that the code you have try is in that workbook. I will look at it tomorrow then for you after work -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... I have alredy added one sheet in the workbook where i have other sheets of customer details like first sheet is "lookup table" with client code in A column and email address in B Second sheet is of one of client code same for second and third I am running macro in look up table sheet and nothing happens No error No Result i have also given name for column A as "customer code" and for B "mail address" What else i have to do......... Can u mail me the macro enabled macro on Thanxs On Jul 29, 3:20 am, "Ron de Bruin" wrote: If nothing happens then this is not correct I think in your test workbook Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
But what is the problem...why its not working
Maybe the code is in the wrong place or ? Mail it to the address on my home page http://www.rondebruin.nl/ -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message ups.com... But what is the problem...why its not working what i need to do for that? & Where i can send the file? i am using dummy data its just a number and my e mail address only On Jul 30, 12:34 am, "Ron de Bruin" wrote: Send me the workbook with the data and your sheet with mail addresses private. Be sure that the code you have try is in that workbook. I will look at it tomorrow then for you after work -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... I have alredy added one sheet in the workbook where i have other sheets of customer details like first sheet is "lookup table" with client code in A column and email address in B Second sheet is of one of client code same for second and third I am running macro in look up table sheet and nothing happens No error No Result i have also given name for column A as "customer code" and for B "mail address" What else i have to do......... Can u mail me the macro enabled macro on Thanxs On Jul 29, 3:20 am, "Ron de Bruin" wrote: If nothing happens then this is not correct I think in your test workbook Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 21, 12:28 am, "Ron de Bruin" wrote: Then Change the D to your column in this code line Set rng = ws1.Range("A1:D" & Rows.Count) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
i mailed u the data file Please chk and revert On Jul 30, 8:54 pm, "Ron de Bruin" wrote: But what is the problem...why its not working Maybe the code is in the wrong place or ? Mail it to the address on my home pagehttp://www.rondebruin.nl/ -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... But what is the problem...why its not working what i need to do for that? & Where i can send the file? i am using dummy data its just a number and my e mail address only On Jul 30, 12:34 am, "Ron de Bruin" wrote: Send me the workbook with the data and your sheet with mail addresses private. Be sure that the code you have try is in that workbook. I will look at it tomorrow then for you after work -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... I have alredy added one sheet in the workbook where i have other sheets of customer details like first sheet is "lookup table" with client code in A column and email address in B Second sheet is of one of client code same for second and third I am running macro in look up table sheet and nothing happens No error No Result i have also given name for column A as "customer code" and for B "mail address" What else i have to do......... Can u mail me the macro enabled macro on Thanxs On Jul 29, 3:20 am, "Ron de Bruin" wrote: If nothing happens then this is not correct I think in your test workbook Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
Hi Ron Its Working Fine......just few points more to make to more userfriendly and protected 1. Want to add few more lines in Body Like Please find attached file Thanxs and regards etc 2. which file i have to take it is as mail macro file. Because i have a seprate file which splited client code wise and other file is of mail address Can i give a open option in mail address file to open splited file or i have to copy macro every time in a module to run it 3 want to protect this file for permanat that no one can change any code in it On Jul 31, 12:06 am, Amol wrote: i mailed u the data file Please chk and revert On Jul 30, 8:54 pm, "Ron de Bruin" wrote: But what is the problem...why its not working Maybe the code is in the wrong place or ? Mail it to the address on my home pagehttp://www.rondebruin.nl/ -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... But what is the problem...why its not working what i need to do for that? & Where i can send the file? i am using dummy data its just a number and my e mail address only On Jul 30, 12:34 am, "Ron de Bruin" wrote: Send me the workbook with the data and your sheet with mail addresses private. Be sure that the code you have try is in that workbook. I will look at it tomorrow then for you after work -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in ooglegroups.com... I have alredy added one sheet in the workbook where i have other sheets of customer details like first sheet is "lookup table" with client code in A column and email address in B Second sheet is of one of client code same for second and third I am running macro in look up table sheet and nothing happens No error No Result i have also given name for column A as "customer code" and for B "mail address" What else i have to do......... Can u mail me the macro enabled macro on Thanxs On Jul 29, 3:20 am, "Ron de Bruin" wrote: If nothing happens then this is not correct I think in your test workbook Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... nothing is happening macro just run and thats all....no result nothing...... what msg i suppose to get after i run this macro On Jul 27, 1:04 am, "Ron de Bruin" wrote: You have the code to split your data so you have seperate sheets now named after the customer code. Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the customer code and in column B the mail address. Try this test macro If it is working OK you can change .Display in the macro to .Send Sub Mail_Every_Worksheet() 'Working in 2000-2007 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim MailAdress As String TempFilePath = Environ$("temp") & "\" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each sh In ThisWorkbook.Worksheets MailAdress = "" On Error Resume Next MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False) On Error GoTo 0 If MailAdress Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = MailAdress .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .display 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in messagenews:1185477964..477262.212100@r34g2000hsd. googlegroups.com... On Jul 27, 12:12 am, "Ron de Bruin" wrote: Yes it is possible but if you are not the only user you must add a lot of error checking If you split the data the sheets will be names after every unique value in the column. This name must me in the seperate workbook so you can use a Vlookup formula to find the sheet name and get the mail address. If you filter on a different column the next time this will not work So your filter column must be fixed and the unique values in this column must be in the Vlookup table -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 26, 9:32 pm, Amol wrote: On Jul 26, 9:27 pm, "Ron de Bruin" wrote: 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 "Amol" wrote in ooglegroups.com... On Jul 23, 2:18 pm, "Ron de Bruin" wrote: 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 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 22, 1:56 am, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in . gbl... 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in oglegroups.com... On Jul 21, 1:27 am, "Ron de Bruin" wrote: 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 ... read more »- Hide quoted text - - Show quoted text - |
I want to Split & save the data in different workbook
you can use the following macro: Sub CopyData() Dim LMainWB As String Dim LNewWB As String Dim LRow As Integer Dim LContinue As Boolean Dim LColAMaster As String Dim LColATest As String Dim LWBCount As Integer Dim LMsg As String Dim LPath As String Dim LFilename As String Dim LColAValue As String 'Path to save all new workbooks to LPath = "C:\" 'Retrieve name of the workbook that contains the data LMainWB = ActiveWorkbook.Name 'Initialize variables LContinue = True LRow = 2 LWBCount = 0 'Start comparing with cell A2 LColAMaster = "A2" 'Loop through all column A values until a blank cell is found While LContinue = True LRow = LRow + 1 LColATest = "A" & CStr(LRow) 'Found a blank cell, do not continue If Len(Range(LColATest).Value) = 0 Then LContinue = False End If 'Value in column A LColAValue = Range(LColAMaster).Value 'Found occurrence that did not match, copy data to new workbook If LColAValue < Range(LColATest).Value Then 'Copy headings Range("A1:D1").Select Selection.Copy 'Add new workbook and paste headings into new workbook Workbooks.Add LNewWB = ActiveWorkbook.Name ActiveSheet.Paste Range("A1").Select 'Copy data from columns A - D Windows(LMainWB).Activate Range(LColAMaster & ":D" & CStr(LRow - 1)).Select Selection.Copy 'Paste results Windows(LNewWB).Activate Range("A2").Select ActiveSheet.Paste Range("A1").Select 'Save (and overwrite, if necessary) workbook with name from column A 'and then close workbook LFilename = LPath & LColAValue & ".xls" If Dir(LFilename) < "" Then Kill LFilename ActiveWorkbook.SaveAs Filename:=LFilename ActiveWorkbook.Close 'Go back to Main sheet and continue where left off Windows(LMainWB).Activate LColAMaster = "A" & CStr(LRow) 'Keep track of the number of workbooks that have been created LWBCount = LWBCount + 1 End If Wend Range("A1").Select Application.CutCopyMode = False LMsg = "Copy has completed. " & LWBCount & " new workbooks have been created." LMsg = LMsg & Chr(10) & "You can find them in the following directory:" & Chr(10) & LPath MsgBox LMsg End Sub *** Sent via Developersdex http://www.developersdex.com *** |
| All times are GMT +1. The time now is 09:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com