Sort by column in another sheet

Hi vba experts
in my file, there are 2 sheets Sheet1 & Sheet2
Sheet1 has data in column a (the original names with the data already sorted in certain way)
Sheet2 has the same names but shuffled with some other data in multiple columns (A1:P200)

I wanted to sort the data in Sheet2 by the data in column a in sheet1

e.g
sheet1
^^^^
aa
bb
cc
dd
11
22
33
44
ee
ff

Sheet2
^^^^
11 ...... any .......... any .........any
cc ...... any .......... any .........any
ee ...... any .......... any .........any
44 ...... any .......... any .........any
aa ...... any .......... any .........any
33 ...... any .......... any .........any
dd ...... any .......... any .........any
22 ...... any .......... any .........any
ff ...... any .......... any .........any
bb ...... any .......... any .........any

As you can achieve it yourself just using a helper column with a MATCH formula
in order to sort the range according to this helper column (the same under VBA) …

A VBA demonstration well working with smart worksheet design for a starting point :

PHP Code: 

Sub Demo1()
        Dim V
    With Sheet2.UsedRange
        V = Application.Match(Sheet1.UsedRange.Columns(1), .Columns(1), 0)
        If Application.Count(V) < UBound(V) Then Beep: Exit Sub
       .Value2 = Application.Index(.Value2, V, Evaluate("COLUMN(" & .Rows(1).Address & ")"))
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thank you. it works well for letter data but not for numbers

As it works like a charm on my side with smart worksheets so the bad is on yours ‼

First this is not my original procedure and you badly modified it !
Your issue is Sheet1 has numbers but Sheet2 has numbers as texts ! Both sheets must have the same data type …

When used Application.Count(V) I got exit sub and the following lines are not executed
The data type is General in both sheets. I couldn't figure it out

I didn't modify anything except count to counta

You are right. So sorry
Now it worked well
Thank you so so much sir

The original file worked well but I got NA at the last row of the result sheet (that I sorted)

No such issue on my side …

Last row # : 11 ?

I don't know what's wrong. I wanted to sort sheet2 as the names in sheet1

Well, well, well … You first forgot to update the column index as in Sheet2 the names are not anymore in column #1 like in your first attachment !

And as I warned it works only with smart worksheets which is not the case in your last attachment
so you must use the CurrentRegion of a cell reference rather than the worksheet UsedRange …

According to post #4 for your post #14 attachment :

PHP Code: 

Sub Demo2()
   Const C = 2
     Dim L&
    With Sheet2.[A1].CurrentRegion.Columns
        .Item(.Count + 1).Formula = "=MATCH(" & .Cells(C).Address(False, False) & "," & _
                                    Sheet1.[A1].CurrentRegion.Columns(1).Address(, , , True) & ",0)"
        .Resize(, .Count + 1).Sort .Item(.Count + 1), 1, Header:=1
         L = Application.Count(.Item(.Count + 1).Offset(1))
        .Item(.Count + 1).ClearContents
         If L < .Rows.Count - 1 Then Range(.Cells(L + 2, C), .Cells(.Rows.Count, C)).Interior.Color = vbRed
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

The last code worked well. Thank you so much sir MarcL

Try it with your post #6 attachment as it is with the constant C set to 1 …