The tourism money argument isn't strong - the French went as far as to guillotine most of their royals, and their exquisite palaces etc. are never short of tourists, and visitors don't have to dodge the royals.
It comes down to sheer tradition, and I think that's allied to the fragility of the British national identity as it's taking a long time for people to get used to the idea the UK is an, at best, middling power nowadays, and really has been at least since Suez.
People have a hard time articulating what "being British" means (and not a few of them have a hard time distinguishing England from the UK), and I reckon that lies at the heart of many of the tensions. I think the US is experiencing similar problems, among its many others.
Whatever my own feelings, there's no prospect of any party coming to power in the foreseeable future on a platform of outright abolishing the Royal Family. I could see it being downgraded in certain respects, perhaps following the model of European and Nordic royal families who hold the titles and some ceremonial functions, but generally live comparatively modestly and normally. I could see a time when the younger generations of royals wouldn't be unhappy with that. Harry's already made his bid for freedom, hiccups and all, and may have eased the way for those family members who'll follow him.
If Andrew does eventually testify, I can't imagine that being seen as heroic - if he'd severed connections and whistleblown years ago, maybe - I think the reaction would be, "About bloody time."
But bear in mind that this is not a particularly bright (nor truthful) man, and he doesn't perform well publicly under pressure - witness his utterly disastrous BBC TV interview with Emily Maitlis, which was intended to salvage his reputation.
= new reply since forum marked as read