It has more to do with it's "lack" of weaknesses than how unwieldy it was. As you mentioned, the tercio was a combination of pikes, musketeers, and sometimes swordsmen. So generally it's an all purpose formation that should stop everything.

So, how would you stop a tercio? Basically you'd have to pour so much lead that you'd need artillery, but artillery in the 16th century was mainly used on ships and for sieges due to their extreme weights. Since they had musketeers to fight other musketeers, pikes to defend from cavalry, and swords to stop any rushed sword attacks, there isn't anything to stop it other than artillery.

During the late 16th and early 17th century do you start to see it wane in usage, mainly due to more mobile artillery, with all of those soldiers clumped together in big squares, it was a walking target.