Sex will always be a big deal.
Sure, we do noble things like make art and enjoy fine dining and go on adventures, but we're more or less gene vehicles. Some of us don't produce offspring but contribute to the species in other ways, like invent things (Tesla) or contribute ideas (Newton) or art (Da Vinci). Or just the guy that does his small part to contribute to society and the economy by working and creating value, then one day dying and leaving his estate to his nieces and nephews or, if they don't exist, to the state.
For most people though, their greatest contribution to the species will be their offspring.
The higher genetic quality their mate, the more superior their offspring.
Because mate quality is so difficult to assess (people lie and inflate mate value any way they can - makeup, clothes, hair, money, game, status, etc.) and happens across so many different dimensions (looks, health, height, intellect, savvy, charisma, creativity, etc.), sex becomes a delicate dance where members of the opposite sex are scrutinizing one another and using offers they've accepted or rebuffed to increase their apparent mate value and get access to even better mating options (e.g., pretty girl gets seen rejecting a bunch of guys - she must be high status and a high caliber mate; cool guy is known to sleep with lots of hot girls - he must have something special about him).
For women, the balance of power shifts dramatically after sex:
Commitment Points: Why You Must Avoid
Once she's sleeping with you, she's more or less stamped you with "Approved for Mating", because at any time, really, you could get her pregnant. Take off the condom, forget to put one on, put one on shoddily and it slips off mid-act, put on a defective one (or one that's too small or too large) that breaks mid-act, go raw but get too excited and fail to pull out, go raw and drip some pre-cum into her, plan on using the Plan B pill but don't use it within 24 hours and efficacy drops to 90% or lower (only 70% if used 72 hours later)... a hundred ways she could end up with your baby in her belly. And once it's there, all bets are off what you do. First world countries lower risk for single mothers by giving them ways to enforce child support payments from fathers, but even then, no guarantee you'll be able to make him pay, and parenting is arguably much more important than financial payments, and no one can force the guy to be a dad.
This reality impacts how sex affects women's social status, with women who hook up easily and a lot essentially telling anyone who will listen "Hey, almost any guy is good enough to make ME pregnant!" which communicates low mate value. Thus, most girls want to restrict their hooking up, while every guy wants to sleep with a girl who sleeps around, but no one wants to marry her, and every girl will mercilessly slut-shame her to differentiate herself and imply that she isn't like that (in other words, that she is picky about mates, and thus high mate value, and thus deserving of a high mate value male).
This also impacts men's status, because if a guy sleeps with lots of girls, that means a lot of girls have stamped him "Genetically Approved." Which means even more women will now want him for their mate. And the hotter and more desirable and less-slutty the women who've stamped him as such, the higher value women presume he must be (thus, why if you tell a new girlfriend that your ex-girlfriend was a raging slut who cheated on you mercilessly she'll recoil in disgust at what a low mate-value man her boyfriend is, whereas if you tell a girlfriend your ex-girlfriend was a beautiful girl with only 2 prior partners, who doted on you and held you in high-esteem, she feels confident her man is a high value one).
That puts men and women into a battle where women's statuses go down as they have sex (unless the man can sleep with her totally discreetly... "off the books" - then he "doesn't count"), while men's go up. The more men a woman sleeps with, the more her ultimate potential mate quality drops (to an extent... she also gets more charisma, bedrooms skills, etc., that help compensate), while the more women a man sleeps with, the more his ultimate potential mate quality rises.
The only time you find sex not a big deal is in communities where sex is divorced from reproduction, like the gay community, where men can not uncommonly have 1000+ lifetime partners if they live in big cities. Even then, it's still kind of a big deal, in that it's something people want a lot and go after a lot. And the pill / condoms don't change this for male-female sex, because even if you
can prevent pregnancy, that doesn't mean you will... women are terrible about taking their pills on time, and just because a guy says he'll pull out doesn't mean he will.
Ultimately... until we start budding again or we all turn asexual, sex'll be a big deal.
Chase