I’ll lay it all out to see if I’m missing something.
1. Meteor is summoned and will kill most everything on the Planet.
2. Shinra, an evil corporation, goes around collecting Huge Materia.
3. The reason for this is to put the Huge Materia into a rocket and then launch the rocket into Meteor in hopes of destroying it and saving the world.
4. The heroes, for no reason that is stated as far as I remember, go around and start stealing the Huge Materia.
5. The rocket is thus less powerful when it crashes into Meteor.
Whether it would work or not is irrelevant because the "good guys" were purposefully destroying an option for saving humanity for…no reason.
So, did I miss something?
All in all the Huge Materia are just a side quest, they have not effect on the plot outside of the episode where they show up, and you don’t even have to save them to progress the game. You do however have to attempt to save each one before the game will progress. So it’s basically a mandatory side quest, with not real with no true moral highground whatsoever. I mean you save some pretty rocks from being broken… and you get your Bahamut summons as a reward…
It’s also the way you get the Master Materia for each category. You bring a Master of Each materia of a given type to the room where the Huge Materia are kept, and you are granted the Master Summon, if you’ve mastered one of every summon, the Master Magic, If you’ve mastered one of every magic, And the Master Command, If you’ve mastered one of every command. If you fail to save the appropriate color huge materia, though, you cannot get the Master Materia if that type… That is unless you kill Emerald Weapon and bring the Earth Harp that it drops to this person in Kalm.(or somethign like that.) But bear in mind that in the original Japanese version, you didn’t fight the weapons, so getting all these materia mastered and taking them to the appropriate Huge Materia, was the only way to get the Master Materia.
As for anything other than an overglorified side quest which in turn gives rise to many overglorified and tedious fetch quests, there really is no point. I usually save all the Huge Materia just to get Ultima and all three Bahamut Summons.