If you have to do it just once, you can left click on a tab and drag it
to a new location between other tabs. Another manual method is to
right click the tab, select Move Or Copy from the window and highlight
the new position.

If you have to do it many times over different files with varying tab
names, it will require a VBA solution.