It might be better as a separate discussion, but how necessary would a Sheffield to Manchester Airport service be if Doncaster Sheffield reopens? I know that Edinburgh, Glasgow and Newcastle have or have had, in Newcastle's case, Manchester Airport services despite having their own airports, although I think Manchester has a wider range of destinations than at least Newcastle and Glasgow.
Robin Hood, DSA, is a red herring and a subject covered in the "Doncaster Sheffield Airport to reopen?" thread.
The passenger volumes in its previous years of operation were modest. Any new services are unlikely to absract large enough numbers from Manchester to impact demand for rail services.
There are too many existing well established destinations and frequencies of flights from Manchester for it to be otherwise. In 25 years time many things may gave changed but without massive population growth to the east of Leeds and Sheffield the numbers don't seem likely to ever stack up.
Of course many travellers on TPE services from Newcastle, Edinburgh and Glasgow do go to Manchester Airport.
Incidentally, on the Sheffield - Manchester route many users are taking advantage of Northern's stopping services. Class 195s are competitive
with TPEs 185s and EMR's 158/170s in a way Pacers could never be. At discounted prices they're taking airport business. Running from the main shed they are a reasonable option for those travelling to or from Manchester for all purposes.
Travelling towards Cardiff, Chester and other destinations from Sheffield a change at Stockport can be a better option than at Piccadilly.