Transferring data

T

tofimoon4

Dear sir,this is my first post to your distinct forum hopping i reac
the ultimate solution because i have sent many messages to differen
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,cod
no.,quan.,price,cost,total amount ) ,i enter the data in these column
daily,what i want from your goodself ( the way can i use to transfe
these data to the agents sheets which contain all above except (agen
column) automatically by ( formula or code) ,i am using excel 2003 an
enclosed herewith an example.
Thanks in advance

+-------------------------------------------------------------------
|Filename: sheets2.zip
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=81
+-------------------------------------------------------------------
 
M

muddan madhu

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
 
D

Don Guillett

I did not see this on codecage but on microsoft.public.excel.misc.
If desired, send your file direct to my address below NON zipped along with
a snippet of this msg on an inserted sheet and complete explanation and
example of what you want.

However, you probably could just keep all on ONE sheet and use
data>filter>autofilter>filter by agent to look at each when desired.
 
S

Sheeloo

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?
 
M

muddan madhu

And for getting the total of invoice

in Cell J5 put this formula and drag it down
=IF(C5="",IF(C5>C6,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.



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

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.
Click to expand...
+-------------------------------------------------------------------+
|Filename: sheets2.zip                                              |
|Download:http://www.thecodecage.com/forumz/attachment.php?attachmentid=81|
+-------------------------------------------------------------------+
Click to expand...
Click to expand...
 
T

tofimoon4

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 formula
3-Paste the code in module and creating a (group) button .when i pres
this button first time ,all the data transferred in excellent way (wit
some differences ),the problem is : when i enter a new data in mai
sheet and press the button to transfer them (the past data transferre
too ) and (the button transferred too to (W) sheet only ).i need you
advice about these steps ,please.
Dear Mr.Don: with thanks and apology ,please let me see Mr.mudda
comments about above notes and decide then
 
D

Don Guillett

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.
 
T

tofimoon4

Dear Mr.Don ,with my appreciation,my e.mail is :[email protected]
Dear Mr.donaldb36 ,thanks for your solution ,but my need is to transfe
data not sortig ,i have many agents sheets and main one as mentioned i
above example,hopping you have seen it.
Thanks to all
 
D

Don Guillett

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.
 
T

tofimoon4

Dear Mr.muddan , i am still waiting and thinking you shall not leave m
at the middle of the way
 
M

muddan madhu

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
 
T

tofimoon4

Dear sir,with my thanks,asking you kindly ( if you have a time) t
implement the code on my example to see ( when i want to transfer an
new data from main to agents sheets ,all the past ones should b
transfered too ) this problem i think is possible to solve if there is
new code for deleting all the data from main after transferring .
sorry for the annoyance
 
M

muddan madhu

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
 
M

muddan madhu

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
 
T

tofimoon4

dear sir,i think i am confused to do any changes ,please can you ( if i
it possible) attach my example with your last adjustment to see where a
i wrong .
Thanks in advance
 
M

muddan madhu

on hurry i did a mistake, replace the below one

old code Rows(a).Copy

new code Rows(a).Cut
 
T

tofimoon4

Dear friend ,many thanks for your help , i think ( because i am new i
VBA ) this subject is so complex for me,and i dont want to steal al
your time ,therefor let the application of the code on my example (whic
i asked for) ,to your time.
my apology
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invoice 8
sheets 4
Invoices details 13
Transfering data 13
Sort 2
Which one to use:Macros/formula/Lookup 8
transferring data 6
Sorting Data from ont sheet to several sheets 11

Top