|
Transferring data
Dear sir,this is my first post to your distinct forum hopping i reach the ultimate solution because i have sent many messages to different forums with no replies, i have a workbook with main sheet and about (10) agents sheets , the main has columns like( date,inv.no,agent name,code no.,quan.,price,cost,total amount ) ,i enter the data in these columns daily,what i want from your goodself ( the way can i use to transfer these data to the agents sheets which contain all above except (agent column) automatically by ( formula or code) ,i am using excel 2003 and enclosed herewith an example. Thanks in advance. +-------------------------------------------------------------------+ |Filename: sheets2.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=81| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
to transfer the data according to Agent, run the below macro.
Please add the sheets with all the agent names mentioned in the main sheet. i.e., Attached file has sheets main,W,Q,R and T was missing please add before running this macro. Note : in the main sheet cell D5 has W and D6 blank once run this macro, in Cell D6 W will placed. Try this one, get back if you any errors. Sub group() Range("D5").Select rng1 = Cells(Rows.Count, "e").End(xlUp).Row For a = 5 To rng1 If ActiveCell.Value < "" Then temp = ActiveCell.Value Else ActiveCell.Value = temp End If If Cells(a, 4) = temp Then Rows(a).Copy Sheets(temp).Select rng = Cells(Rows.Count, "d").End(xlUp).Row Cells(rng + 1, 1).Select ActiveSheet.Paste Sheets("main").Select Else End If Cells(a + 1, 4).Select Next Application.CutCopyMode = False End Sub And for getting the total of invoice in Cell On Feb 21, 11:56*pm, tofimoon4 wrote: Dear sir,this is my first post to your distinct forum hopping i reach the ultimate solution because i have sent many messages to different forums with no replies, i have a workbook with main sheet and about (10) agents sheets , the main has columns like( date,inv.no,agent name,code no.,quan.,price,cost,total amount ) ,i enter the data in these columns daily,what i want from your goodself ( the way can i use to transfer these data to the agents sheets which contain all above except (agent column) automatically by ( formula or code) ,i am using excel 2003 and enclosed herewith an example. Thanks in advance. +-------------------------------------------------------------------+ |Filename: sheets2.zip * * * * * * * * * * * * * * * * * * * * * * *| |Download:http://www.thecodecage.com/forumz/attachment.php?attachmentid=81| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Can not access the file you have posted as it requires a password. Try
posting it on Wikisend.com. Do you want to transfer ALL the rows in the master whenever you want or you just want to add the new rows to the right agent sheet? How are the agent sheets named? How much do you know about macros? "tofimoon4" wrote: Dear sir,this is my first post to your distinct forum hopping i reach the ultimate solution because i have sent many messages to different forums with no replies, i have a workbook with main sheet and about (10) agents sheets , the main has columns like( date,inv.no,agent name,code no.,quan.,price,cost,total amount ) ,i enter the data in these columns daily,what i want from your goodself ( the way can i use to transfer these data to the agents sheets which contain all above except (agent column) automatically by ( formula or code) ,i am using excel 2003 and enclosed herewith an example. Thanks in advance. +-------------------------------------------------------------------+ |Filename: sheets2.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=81| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
And for getting the total of invoice
in Cell J5 put this formula and drag it down =IF(C5="",IF(C5C6,C5,J4),C5) and now in cell I5 put this formula and drag it down =SUMIF($J$5:$J$15,C5,$H$5:$H$15) If u want you can hide the column J. On Feb 22, 1:55*am, muddan madhu wrote: to transfer the data according to Agent, run the below macro. Please add the sheets with all the agent names mentioned in the main sheet. i.e., Attached file has sheets main,W,Q,R and T was missing please add before running this macro. Note : in the main sheet cell D5 has W and D6 blank once run this macro, in Cell D6 W will placed. Try this one, get back if you any errors. Sub group() Range("D5").Select rng1 = Cells(Rows.Count, "e").End(xlUp).Row For a = 5 To rng1 * *If ActiveCell.Value < "" Then * * * *temp = ActiveCell.Value * *Else * * * *ActiveCell.Value = temp * *End If If Cells(a, 4) = temp Then * *Rows(a).Copy * *Sheets(temp).Select * *rng = Cells(Rows.Count, "d").End(xlUp).Row * *Cells(rng + 1, 1).Select * *ActiveSheet.Paste * *Sheets("main").Select Else * *End If Cells(a + 1, 4).Select Next Application.CutCopyMode = False End Sub And for getting the total of invoice in Cell On Feb 21, 11:56*pm, tofimoon4 wrote: Dear sir,this is my first post to your distinct forum hopping i reach the ultimate solution because i have sent many messages to different forums with no replies, i have a workbook with main sheet and about (10) agents sheets , the main has columns like( date,inv.no,agent name,code no.,quan.,price,cost,total amount ) ,i enter the data in these columns daily,what i want from your goodself ( the way can i use to transfer these data to the agents sheets which contain all above except (agent column) automatically by ( formula or code) ,i am using excel 2003 and enclosed herewith an example. Thanks in advance. +-------------------------------------------------------------------+ |Filename: sheets2.zip * * * * * * * * * * * * * * * * * * * * * * *| |Download:http://www.thecodecage.com/forumz/attachment.php?attachmentid=81| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Many thanks to all the friends who advanced their help . Dear Mr.muddan : i followed all the steps you have advised as below: 1-Creating (T) agent sheet 2-Entering the two excellent formulas 3-Paste the code in module and creating a (group) button .when i press this button first time ,all the data transferred in excellent way (with some differences ),the problem is : when i enter a new data in main sheet and press the button to transfer them (the past data transferred too ) and (the button transferred too to (W) sheet only ).i need your advice about these steps ,please. Dear Mr.Don: with thanks and apology ,please let me see Mr.muddan comments about above notes and decide then. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
I have used your data and designed a workbook that fills in the blanks and the invoice total and lets you use auto filter on ONE sheet. Send your email address to my address and I'll send the file. -- Don Guillett Microsoft MVP Excel SalesAid Software "tofimoon4" wrote in message ... Many thanks to all the friends who advanced their help . Dear Mr.muddan : i followed all the steps you have advised as below: 1-Creating (T) agent sheet 2-Entering the two excellent formulas 3-Paste the code in module and creating a (group) button .when i press this button first time ,all the data transferred in excellent way (with some differences ),the problem is : when i enter a new data in main sheet and press the button to transfer them (the past data transferred too ) and (the button transferred too to (W) sheet only ).i need your advice about these steps ,please. Dear Mr.Don: with thanks and apology ,please let me see Mr.muddan comments about above notes and decide then. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
See file attached which uses autofilter instead of separate sheets. +-------------------------------------------------------------------+ |Filename: tofinmoon.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=82| +-------------------------------------------------------------------+ -- donaldb36 ------------------------------------------------------------------------ donaldb36's Profile: http://www.thecodecage.com/forumz/member.php?userid=143 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Dear Mr.Don ,with my appreciation,my e.mail is Dear Mr.donaldb36 ,thanks for your solution ,but my need is to transfer data not sortig ,i have many agents sheets and main one as mentioned in above example,hopping you have seen it. Thanks to all. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
If you take some time to play with the autofilter you will see the
advantages of keeping it together. You can get so much more information from the data that way. Some would advocate a pivot table. -- Don Guillett Microsoft MVP Excel SalesAid Software "tofimoon4" wrote in message ... Dear Mr.Don ,with my appreciation,my e.mail is Dear Mr.donaldb36 ,thanks for your solution ,but my need is to transfer data not sortig ,i have many agents sheets and main one as mentioned in above example,hopping you have seen it. Thanks to all. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Dear Mr.muddan , i am still waiting and thinking you shall not leave me at the middle of the way. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Sorry, I didn't check your reply.
alternate solution : try this macro First it will ask from which row the macro need to run, you need type the row number as 5 or 10...... Sub group() rowno = (InputBox("type the row number")) rng1 = Cells(Rows.Count, "e").End(xlUp).Row For a = rowno To rng1 If ActiveCell.Value < "" Then temp = ActiveCell.Value Else ActiveCell.Value = temp End If If Cells(a, 4) = temp Then Rows(a).Copy Sheets(temp).Select rng = Cells(Rows.Count, "d").End(xlUp).Row Cells(rng + 1, 1).Select ActiveSheet.Paste Sheets("main").Select Else End If Cells(a + 1, 4).Select Next Application.CutCopyMode = False End Sub On Feb 24, 12:33*am, tofimoon4 wrote: Dear Mr.muddan , i am still waiting and thinking you shall not leave me at the middle of the way. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Dear sir,with my thanks,asking you kindly ( if you have a time) to implement the code on my example to see ( when i want to transfer any new data from main to agents sheets ,all the past ones should be transfered too ) this problem i think is possible to solve if there is a new code for deleting all the data from main after transferring . sorry for the annoyance. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
add this code at the beginning
Sheets(Array("W", "Q", "R", "T")).Select Sheets("W").Activate r1 = Cells(Rows.Count, "e").End(xlUp).Row If r1 = 4 Then Rows("4:" & r1).Select Selection.ClearContents Else End If Sheets("main").Select On Feb 24, 3:58*pm, tofimoon4 wrote: Dear sir,with my thanks,asking you kindly ( if you have a time) to implement the code on my example to see ( when i want to transfer any new data from main to agents sheets ,all the past ones should be transfered too ) this problem i think is possible to solve if there is a new code for deleting all the data from main after transferring . sorry for the annoyance. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Or this with slight change in if condition
Sub group() Range("D5").Select rng1 = Cells(Rows.Count, "e").End(xlUp).Row Sheets(Array("W", "Q", "R", "T")).Select Sheets("W").Activate Rows("4:" & rng1).Select Selection.ClearContents Sheets("main").Select For a = 5 To rng1 If ActiveCell.Value < "" Then temp = ActiveCell.Value Else ActiveCell.Value = temp End If If Cells(a, 4) = temp Then Rows(a).Copy Sheets(temp).Select rng = Cells(Rows.Count, "d").End(xlUp).Row Cells(rng + 1, 1).Select ActiveSheet.Paste Sheets("main").Select Else End If Cells(a + 1, 4).Select Next Application.CutCopyMode = False End Sub On Feb 24, 4:37*pm, muddan madhu wrote: add this code at the beginning * *Sheets(Array("W", "Q", "R", "T")).Select * * Sheets("W").Activate * * r1 = Cells(Rows.Count, "e").End(xlUp).Row * * If r1 = 4 Then * * Rows("4:" & r1).Select * * Selection.ClearContents * * Else * * End If * * Sheets("main").Select On Feb 24, 3:58*pm, tofimoon4 wrote: Dear sir,with my thanks,asking you kindly ( if you have a time) to implement the code on my example to see ( when i want to transfer any new data from main to agents sheets ,all the past ones should be transfered too ) this problem i think is possible to solve if there is a new code for deleting all the data from main after transferring . sorry for the annoyance. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=67250- Hide quoted text - - Show quoted text - |
Transferring data
dear sir,i think i am confused to do any changes ,please can you ( if is it possible) attach my example with your last adjustment to see where am i wrong . Thanks in advance. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
on hurry i did a mistake, replace the below one
old code Rows(a).Copy new code Rows(a).Cut On Feb 24, 4:58*pm, tofimoon4 wrote: dear sir,i think i am confused to do any changes ,please can you ( if is it possible) attach my example with your last adjustment to see where am i wrong . Thanks in advance. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile:http://www.thecodecage.com/forumz/member.php?userid=138 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
Transferring data
Dear friend ,many thanks for your help , i think ( because i am new in VBA ) this subject is so complex for me,and i dont want to steal all your time ,therefor let the application of the code on my example (which i asked for) ,to your time. my apology. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67250 |
| All times are GMT +1. The time now is 06:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com