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

 

[ravishankar]

HI
Paste the following codes in the macro window (Alt F11)

sub sort()
x = cells(rows.count,1).end(xlUp).row
for a = 1 to x
b = cells(a,2)
y = worksheets(b).cells(rows.count,1).end(xlUp).row
rows(a).copy
worksheets(b).rows(y+1).pastespecial
next a
Msgbox " completed"
end sub

run the macro
Ravi

 

[Vicky6615]

Hi Ravi !

Thanks allot for paying attention to my problem..

I have Copied the code in macro window, but while running it i am getting an erroe msg :

Run-time error '9':

Subscript out of range.

Kindly debug it..

Thanks once again.

 

[Dylan]

The sheet name is combine by the Staff and Seg, try this..

sub sort() 
x = cells(rows.count,1).end(xlUp).row 
for a = 1 to x 
b = cells(a,2) & " " & cells(a,1)
y = worksheets(b).cells(rows.count,1).end(xlUp).row 
rows(a).copy 
worksheets(b).rows(y+1).pastespecial 
next a 
Msgbox " completed" 
end sub

 

[Vicky6615]

Hi Ravi !

The Problem remain the same...

Run-time error '9':

Subscript out of range.

One thing i want to make you notice about is that, when i am using the breakpoint to trace the error, the highlighted line is showing the error

sub sort()
x = cells(rows.count,1).end(xlUp).row
for a = 1 to x
b = cells(a,2) & " " & cells(a,1)
y = worksheets(b).cells(rows.count,1).end(xlUp).row
rows(a).copy
worksheets(b).rows(y+1).pastespecial
next a
Msgbox " completed"
end sub

the value for "y" is showing me empty

I think this will help you to solve the problem

Thanks once again

 

[Dylan]

Showing 0, means that it can't find the named sheet.
Is your sheets named as
"Ajj A"
"Ajj B"
"Ajj C"
"Ajj D"
and so on....

If yes, please check 2 thing.
1st make sure you enter "Ajj", "Cas" in column B without any additional space.
2nd make sure you enter "A" or "B" or "C" or "D" in column A without any additional space.

 

[Vicky6615]

Hello Dylan !

Thank you very much, i am really greatful to get your feedback.

I have checked the things you have mentioned, after rectifying it what i have found is :

The entire rows is getting pasted to desired sheet,but again there is a problem :

1) As i am running the macro, the row gets pasted and the same error windows comes saying "Run-time error '9': Subscript out of range." and it is not geeting out of Loop which bind me to work further.

2) Suppose, i have a prescribed form, as i have mentioned the format in my forst post.

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

i want the row should get paste in that format, but the rows are geeting posted at the end of the format.


3) It is not checking bought & sold part, as i have mentioned in my first post, My format of sheets has Two Side " Bought Side , Sold Side"

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.


4) I dont want entire row to be pasted, I want particular range of cell to be pasted in the sheet.

Thanks

 

[Vicky6615]

Hello Dylan !

I know i have made you puzzled.

Thats the way i have stucked into..

Kindly Help..

Thank you

 

[Vicky6615]

Hello Board !


Can anyone plz help me to get rid off the problem


Thanks

 

[Vicky6615]

Somebody Help Me pleaseeeeeeeeeeeeeeee. !