My Change Event code for those who are interested...

The current version below does not handles any errors, mistypes, undos etc.



'-------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Long
Dim c As Long
Dim m As Long
Dim t As Long
Dim b As String

m = Range("h1").Value + 2

If Len(Cells(m, 1)) = 0 Then _
Exit Sub


If Len(Cells(m, 2)) > 0 And Len(Cells(m, 3)) > 0 And (Len(Cells(m, 4)) >
0 Or Len(Cells(m, 5)) > 0) Then

b = Cells(m, 3).Value
t = Worksheets(b).Range("h1").Value + 2


Worksheets(b).Cells(t, 1) = Cells(m, 1)
Worksheets(b).Cells(t, 2) = Cells(m, 2)
Worksheets(b).Cells(t, 3) = Cells(m, 3)

If Len(Cells(m, 4)) > 0 Then
Worksheets(b).Cells(t, 5) = Cells(m, 4)
Else
Worksheets(b).Cells(t, 4) = Cells(m, 5)
End If


Range("h1").Value = Range("h1").Value + 1
Worksheets(b).Range("h1").Value = Worksheets(b).Range("h1").Value + 1

End If

End Sub
'-------------------------------------------------------------------------

First - NEVER send a workbook to this newsgroup. If someone wants to see
your workbook they will invite you to send to them direct. AND, please stay
in the ORIGINAL thread for continuity.
Second - It was suggested that you just keep all on the same worksheet and
use
Data>filter>autofilter to see the individual col C item desired. You can use
subtotal to get the totals, etc

Third- If you insist on doing it this way, it could be MUCH easier than the
way you have done it. Use autofilter!

It appears that you should seek professional help from one of us.

--
Don Guillett
SalesAid Software
[email protected]
"serdar" <[email protected]> wrote in message
news:[email protected]...
> The current version below does not handles any errors, mistypes, undos
etc.
>
>
>
> '-------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim x As Long
> Dim c As Long
> Dim m As Long
> Dim t As Long
> Dim b As String
>
> m = Range("h1").Value + 2
>
> If Len(Cells(m, 1)) = 0 Then _
> Exit Sub
>
>
> If Len(Cells(m, 2)) > 0 And Len(Cells(m, 3)) > 0 And (Len(Cells(m, 4))
>
> 0 Or Len(Cells(m, 5)) > 0) Then
>
> b = Cells(m, 3).Value
> t = Worksheets(b).Range("h1").Value + 2
>
>
> Worksheets(b).Cells(t, 1) = Cells(m, 1)
> Worksheets(b).Cells(t, 2) = Cells(m, 2)
> Worksheets(b).Cells(t, 3) = Cells(m, 3)
>
> If Len(Cells(m, 4)) > 0 Then
> Worksheets(b).Cells(t, 5) = Cells(m, 4)
> Else
> Worksheets(b).Cells(t, 4) = Cells(m, 5)
> End If
>
>
> Range("h1").Value = Range("h1").Value + 1
> Worksheets(b).Range("h1").Value = Worksheets(b).Range("h1").Value + 1
>
> End If
>
> End Sub
> '-------------------------------------------------------------------------
>
>
>

Thanks for ur reply. I will comment on your steps below.


"Don Guillett" <[email protected]>, haber iletisinde şunları
yazdı:eEN#[email protected]...
> First - NEVER send a workbook to this newsgroup. If someone wants to see
> your workbook they will invite you to send to them direct. AND, please
stay
> in the ORIGINAL thread for continuity.


I remember such newsgroups of microsoft does not allow to send attachments,
i just tried if this is true or not. Apparently i was wrong, sorry, and yes
I should keep the original thread as well.


> Second - It was suggested that you just keep all on the same worksheet and
> use
> Data>filter>autofilter to see the individual col C item desired. You can
use
> subtotal to get the totals, etc

I am doing this for one of my friend, he asked me to do it this way.
Probably he is willing to use this feature elsewhere.
As i know, filtering does not helps when u want to use filtered data
elsewhere for different calculation purposes (i may be wrong cos i dont use
excel professionally). Our case does not seems to be a "monitoring data
only" case.


>
> Third- If you insist on doing it this way, it could be MUCH easier than
the
> way you have done it. Use autofilter!
>
> It appears that you should seek professional help from one of us.


Not too complicated task to pay for i assume and also this exceeds my
initiative.


Finally, i am from Turkey and sorry for my English if smt is unclear in my
messages.

It is NOT necessary to filter to perform calculations on data. The subtotal
will work well on filtered data and sumproduct will do many calculations
without even the bother of filtering.

--
Don Guillett
SalesAid Software
[email protected]
"serdar" <[email protected]> wrote in message
news:[email protected]...
> Thanks for ur reply. I will comment on your steps below.
>
>
> "Don Guillett" <[email protected]>, haber iletisinde şunları
> yazdı:eEN#[email protected]...
> > First - NEVER send a workbook to this newsgroup. If someone wants to see
> > your workbook they will invite you to send to them direct. AND, please
> stay
> > in the ORIGINAL thread for continuity.
>
>
> I remember such newsgroups of microsoft does not allow to send
attachments,
> i just tried if this is true or not. Apparently i was wrong, sorry, and
yes
> I should keep the original thread as well.
>
>
> > Second - It was suggested that you just keep all on the same worksheet
and
> > use
> > Data>filter>autofilter to see the individual col C item desired. You can
> use
> > subtotal to get the totals, etc
>
> I am doing this for one of my friend, he asked me to do it this way.
> Probably he is willing to use this feature elsewhere.
> As i know, filtering does not helps when u want to use filtered data
> elsewhere for different calculation purposes (i may be wrong cos i dont
use
> excel professionally). Our case does not seems to be a "monitoring data
> only" case.
>
>
> >
> > Third- If you insist on doing it this way, it could be MUCH easier than
> the
> > way you have done it. Use autofilter!
> >
> > It appears that you should seek professional help from one of us.
>
>
> Not too complicated task to pay for i assume and also this exceeds my
> initiative.
>
>
> Finally, i am from Turkey and sorry for my English if smt is unclear in my
> messages.
>
>
>
>

I have managed to make my code undoable so if u mistype any value and
retype, it automatically updates the appropriate value in the related
worksheet.

Next I' m gonna add smt to handle deleting a whole row of record which
deletes the appropriate rec. on the rel. worksheet and shiftes the rest of
the list.

In the end if the user only enters data in the master worksheet there seems
to be no problem at all. What do you think? If any problems i am not aware,
let me know.

Here is the code if u like.. (no comments, thus a little tricky to
understand the variables





Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Long
Dim c As Long
Dim m As Long
Dim t As Long
Dim b As String

m = Range("h1").Value + 2
x = 2
c = 1



If Target.Column < 6 And Target.Row < m And Target.Row > 1 Then

b = Cells(Target.Row, 3)

Do While c > 0

If Worksheets(b).Cells(x, 6) = "" Then _
c = 0


If Worksheets(b).Cells(x, 6) = Cells(Target.Row, 6) Then


Select Case Target.Column
Case Is = 4
Worksheets(b).Cells(x, 5) = Target
Case Is = 5
Worksheets(b).Cells(x, 4) = Target
Case Else
Worksheets(b).Cells(x, Target.Column) = Target
End Select


End If


x = x + 1

Loop

End If





If Len(Cells(m, 1)) = 0 Then _
Exit Sub






If Len(Cells(m, 2)) > 0 And Len(Cells(m, 3)) > 0 And (Len(Cells(m, 4)) >
0 Or Len(Cells(m, 5)) > 0) Then

b = Cells(m, 3).Value
t = Worksheets(b).Range("h1").Value + 2


Worksheets(b).Cells(t, 1) = Cells(m, 1)
Worksheets(b).Cells(t, 2) = Cells(m, 2)
Worksheets(b).Cells(t, 3) = Cells(m, 3)

If Len(Cells(m, 4)) > 0 Then
Worksheets(b).Cells(t, 5) = Cells(m, 4)
Else
Worksheets(b).Cells(t, 4) = Cells(m, 5)
End If


Range("h1").Value = Range("h1").Value + 1
Worksheets(b).Range("h1").Value = Worksheets(b).Range("h1").Value + 1

Cells(m, 6) = m - 1
Worksheets(b).Cells(t, 6) = m - 1

End If

End Sub

Again, you should use autofilter on ONE database but IF you insist, try this
without your h1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then Exit Sub
x = Target.Row
If Application.CountA(Range(Cells(x, 1), Cells(x, 5))) = 4 Then
y = Cells(x, 3)
z = Sheets(y).Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows(x).Copy Sheets(y).Rows(z)
End If
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"serdar" <[email protected]> wrote in message
news:[email protected]...
> I have managed to make my code undoable so if u mistype any value and
> retype, it automatically updates the appropriate value in the related
> worksheet.
>
> Next I' m gonna add smt to handle deleting a whole row of record which
> deletes the appropriate rec. on the rel. worksheet and shiftes the rest of
> the list.
>
> In the end if the user only enters data in the master worksheet there
seems
> to be no problem at all. What do you think? If any problems i am not
aware,
> let me know.
>
> Here is the code if u like.. (no comments, thus a little tricky to
> understand the variables
>
>
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim x As Long
> Dim c As Long
> Dim m As Long
> Dim t As Long
> Dim b As String
>
> m = Range("h1").Value + 2
> x = 2
> c = 1
>
>
>
> If Target.Column < 6 And Target.Row < m And Target.Row > 1 Then
>
> b = Cells(Target.Row, 3)
>
> Do While c > 0
>
> If Worksheets(b).Cells(x, 6) = "" Then _
> c = 0
>
>
> If Worksheets(b).Cells(x, 6) = Cells(Target.Row, 6) Then
>
>
> Select Case Target.Column
> Case Is = 4
> Worksheets(b).Cells(x, 5) = Target
> Case Is = 5
> Worksheets(b).Cells(x, 4) = Target
> Case Else
> Worksheets(b).Cells(x, Target.Column) = Target
> End Select
>
>
> End If
>
>
> x = x + 1
>
> Loop
>
> End If
>
>
>
>
>
> If Len(Cells(m, 1)) = 0 Then _
> Exit Sub
>
>
>
>
>
>
> If Len(Cells(m, 2)) > 0 And Len(Cells(m, 3)) > 0 And (Len(Cells(m, 4))
>
> 0 Or Len(Cells(m, 5)) > 0) Then
>
> b = Cells(m, 3).Value
> t = Worksheets(b).Range("h1").Value + 2
>
>
> Worksheets(b).Cells(t, 1) = Cells(m, 1)
> Worksheets(b).Cells(t, 2) = Cells(m, 2)
> Worksheets(b).Cells(t, 3) = Cells(m, 3)
>
> If Len(Cells(m, 4)) > 0 Then
> Worksheets(b).Cells(t, 5) = Cells(m, 4)
> Else
> Worksheets(b).Cells(t, 4) = Cells(m, 5)
> End If
>
>
> Range("h1").Value = Range("h1").Value + 1
> Worksheets(b).Range("h1").Value = Worksheets(b).Range("h1").Value + 1
>
> Cells(m, 6) = m - 1
> Worksheets(b).Cells(t, 6) = m - 1
>
> End If
>
> End Sub
>
>
>
>
>
>
>

what is it for? it substitutes my total record cell (h1) right?
-my friend insists on doing it this way. i showed him ur comments too

"Don Guillett" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> Again, you should use autofilter on ONE database but IF you insist, try
this
> without your h1
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column < 4 Then Exit Sub
> x = Target.Row
> If Application.CountA(Range(Cells(x, 1), Cells(x, 5))) = 4 Then
> y = Cells(x, 3)
> z = Sheets(y).Cells(Rows.Count, 1).End(xlUp).Row + 1
> Rows(x).Copy Sheets(y).Rows(z)
> End If
> End Sub
>

This code waits until you have 4 items out of five on the row. It then
copies the entire row (x) to the next available row (which is z) for the
sheet indicated in col C which is (y).
You need do nothing with your h1 or anything else.

BTW. This is STILL not the best way to do this. In fact, far from it.

--
Don Guillett
SalesAid Software
[email protected]
"serdar" <[email protected]> wrote in message
news:%23LCOw%[email protected]...
> what is it for? it substitutes my total record cell (h1) right?
> -my friend insists on doing it this way. i showed him ur comments too
>
> "Don Guillett" <[email protected]>, haber iletisinde şunları
> yazdı:[email protected]...
> > Again, you should use autofilter on ONE database but IF you insist, try
> this
> > without your h1
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column < 4 Then Exit Sub
> > x = Target.Row
> > If Application.CountA(Range(Cells(x, 1), Cells(x, 5))) = 4 Then
> > y = Cells(x, 3)
> > z = Sheets(y).Cells(Rows.Count, 1).End(xlUp).Row + 1
> > Rows(x).Copy Sheets(y).Rows(z)
> > End If
> > End Sub
> >
>
>