You don't really need VBA for this as formulae could do the same pretty efficiently.

Using your sample change Hierarchy sheet as follows:

B2:
=INDEX(Ranking!$A:$A,MATCH(COLUMNS($B2:B2),Ranking!$B:$B,0))
copied to G2

B1:
=MATCH(B$2,Raw_Data!$2:$2,0)
copied to G1

B3:
=INDEX(Raw_Data!3:3,B$1)
copied to matrix B3:G9
As you alter Ranking list so the Hierarchy sheet will update accordingly.