Copy sorted data from one sheet onto multiple sheets

[marimar02]

Hello,

I need a simple macro that will:

1. Sort Sheet 1 based on Number in column B and then in column A (note Sheets are named based on #s available in column A, then
2. Copy data from Column B to Sheets names corresponding to column A numbers

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]30[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]30[/TD]
[TD]22222[/TD]
[/TR]
</tbody>[/TABLE]

So, anything in column B with a #10 in column A, will be copied over to Sheet named "A". Same should repeat for any number #20 in A and so on until all Sheets have been pasted.

Thank you very much...

 

[DanteAmor]

So, anything in column B with a #10 in column A, will be copied over to Sheet named "A".
Thank you very much...

You mean copied over to Sheet named "10"?

Then, these values ​​will be copied to sheet "10"?

A B
1 10 11111
2 10 22222
3 10 33333
4 10 44444

these values ​​will be copied to sheet "20"?



5 20 11111
6 20 22222

etc...

 

[Akuini]

Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1084315a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1084315-copy-sorted-data-one-sheet-onto-multiple-sheets.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"A1:A"[/COLOR] & n)
Range(Cells([COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(n, [COLOR=brown]"B"[/COLOR])).Sort key1:=Range([COLOR=brown]"A1"[/COLOR]), order1:=xlAscending, key2:=Range([COLOR=brown]"B1"[/COLOR]), order2:=xlAscending, _
            Header:=xlNo, Orientation:=xlTopToBottom
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        b = Range([COLOR=brown]"A:A"[/COLOR]).Find(va(i, [COLOR=crimson]1[/COLOR]), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheets([COLOR=Royalblue]CStr[/COLOR](va(i, [COLOR=crimson]1[/COLOR]))).Range([COLOR=brown]"A1:A"[/COLOR] & b - i + [COLOR=crimson]1[/COLOR]).Value = Range([COLOR=brown]"B"[/COLOR] & i & [COLOR=brown]":B"[/COLOR] & b).Value
        i = b
    [COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]True
[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[marimar02]

exactly

 

[DanteAmor]

Goes the macro

Sub mia()
    Dim datos As Range, i As Long, n As Long
    Set datos = Range("A1").CurrentRegion
    datos.Sort key1:=Range("A1"), order1:=xlAscending, key2:=Range("B1"), order2:=xlAscending, Header:=xlNo
    For i = 1 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        Sheets(CStr(datos(i, 1))).Range("A1").Resize(n, 1).Value = datos(i, 1).Resize(n, 1).Offset(, 1).Value
        i = i + n - 1
    Next
End Sub

Let me know if you have some question.