Automatically Copying Cell to Other Sheet on a condition

[Vicky6615]

Hello Board !

Thank u all for always paying attention to my problems :

I have got a situation here.. Pls Try to solve it.

I have 41 Sheet in my Workbook.

Sheet1 Contains all the Data

For Example:

Seg....Staff......Toys......Date......Rate......Qty......Value
A...... Ajj..........AJ...... 02/07...... 100...... 2....... 200
A...... Ajj.......... JJ...... 02/07....... 50...... -6...... -300
B...... Cas.......DF...... 02/07....... 60...... 10...... 600
C...... Las........ER...... 02/07...... 500...... 1...... 500
C...... Dud........LT...... 02/07...... 200...... 2...... 400
A...... Cas........OM...... 02/07...... 150...... -2...... -300
D...... Tom........WE...... 02/07...... 100...... 4...... 400

And so on........

Now For every Staff I have Four Individual Sheet of "Seg"

For Example:

Sheet2 : Ajj A, Sheet10 : Las A
Sheet3 : Ajj B, Sheet11 : Las B
Sheet4 : Ajj C, Sheet12 : Las C
Sheet5 : Ajj D, Sheet13 : Las D
Sheet6 : Cas A, Sheet14 : Dud A
Sheet7 : Cas B, Sheet15 : Dud B
Sheet8 : Cas C, Sheet16 : Dud C
Sheet9 : Cas D, Sheet17 : Dud D
And so on.........

Format of each Sheet is as follow :

Toys.......................Bought........................................Sold............
.................Date....Rate....Qty....Value........Date....Rate....Qty....Value

What I want is to Copy Data From Sheet1 to each sheet of the Staff according to there Respective Details.

For Example:

Lets take Third & Sixth record of sheet1

Cas Bought 10pcs of DF @ 60 on 02/07 of Segment B,
Cas Sold 2Pcs of OM @ 150 on 02/07 of Segment A

I want is :
1st record to be copied automatically to Sheet7 (CAS B) in Bought Side Column,
2nd record to be copied automatically to Sheet6 (CAS A) in Sold Side Column
with name of toys in both the sheet.


Kindly Help

Thanks

 

[Dylan]

Sorry, I did not notice your reply.
Please try this code.

Sub sort()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
b = Cells(a, 2) & " " & Cells(a, 1)

y = Worksheets(b).Cells(Rows.Count, 1).End(xlUp).Row

If y = 1 Then
    y = 2
Else
    y = y
End If

BoughtOrSold = Cells(a, 6).Value
If BoughtOrSold < 0 Then
    'Sold
    Col = 5
Else
    'Bought
    Col = 1
End If

    Worksheets(b).Cells(y + 1, 1).Value = Cells(a, 1).Value
For I = 1 To 4
    Worksheets(b).Cells(y + 1, Col + I).Value = Cells(a, I + 3).Value
Next I

Next a
MsgBox " completed"
End Sub

 

[Vicky6615]

Thanks allot dylan !

I have used the code sent by you, it is working nearest to my expectation, but still its is not perfect to my desired result

There are some issues i want to discuss it with you

1) when ever transformation complete the data get pasted absolutely fine as i want but it get pasted from 31st row of individual sheet, because there are some data till 31st row, which i dont want

I have a blank space from 6th row to 22nd row and i want the data should get pasted to these blank space as it is instead of 31st row.

2) As i am entering data in sheet name "working and click transform, the data get segregated to individual sheet,
now as i add some more data in the sheet name "working" and click transform this time the existing data and the data i have entered both again gets segregated which i dont want.
I want to overwrite the existing data and add new data to the individual sheet.

I have a solution to the problem, but i dont know how to apply it.

The solution is:

When ever the data get transformed, before transformation it should erase the data ranging from 6th row 22nd row, but not the format only data. and repaste the transformation result.

I think these will solve my purpose.

3) after the transformation I want the data should get sorted from 6th row to 22nd row on

"Name of toys" i.e. Column "A" then Date Column "B" On indvidual Sheet for Boght side
"Name of toys" i.e. Column "A" then Date Column "F" On indvidual Sheet for Sell side

Thanks

 

[Vicky6615]

Hi Dylan

This is the format of the individual sheet for an example.

You can see the transformated result at the end of the format which i dont want and
the desired result of the transformation at row 5 which i want.

Copy (2) of Abt Sheet.xls
	A	B	C	D	E	F	G	H	I	J
1	Anita Mishra
2	Capital Market Transaction for the Month of August 2007
3	Security Name	Bought For You				Sold For You				Profit/Loss
4		Date	Price	Qty	Value	Date	Price	Qty	Value
5	RPL	02/07/2007	100.2000	100	10020.00	02/07/2007	99.8000	-100	9980.00
6
7
8
9
10
11
12
13
14
15	Gross Profit From Capital Market In The Month of August 2007
16
17	Service Tax
18	Securities Transaction Tax
19	Transaction Charges
20	Stamp Charges
21	Net Profit From Capital Market In The Month of August 2007
22	RPL	02/07/2007	100.2000	100	10020.00
23	RPL					02/07/2007	99.8000	-100	9980
Anita CM

 

[Dylan]

Hi Vicky,

I need some data for me to try out my code.
If you don't mind please send your file to me. Will pm my email to you.

regards,
Dylan