The end of the first episode, "Encounter at Farpoint" when the two aliens are reunited. Awful episode but damn, that scene was heartwarming (even with Troi's sappiness and the cheesy music.)
From the same episode, Admiral McCoy's cameo and his words of wisdom to Data: "You treat her like a lady, and she'll always bring you home." Feel free to sob into a pillow.
It's even more sad/heartwarming considering this was DeForest Kelley's final appearance in any Star Trek television series.
"The Measure of a Man" when Data comes to cheer up Riker, who was kicking himself about the role he was forced to play, the prosecutor that could have condemned the android. To do so, Data tells him that he is not only well aware that Riker had no choice in the matter, but will never forget that making that sacrifice gave him his one chance to win his freedom with the perfect exchange:
Data: That action hurt you, and saved me. I will not forget it. Riker: You're a wise man, my friend. Data: Not yet, sir, but with your help, I am learning.
Earlier in the episode Picard tells Data about the forthcoming hearing:
Picard: Now, I have been asked to represent you, but if there is some other officer with which you would feel more happy...?
Data: Captain, I have complete confidence in your ability to represent my interests.
During the trial, Picard showcases a holographic picture of Tasha that Data owns. Picard asks Data to explain to the court why he keeps it.
Data: I would prefer not to answer, sir. I gave my word. Picard: Under the circumstances, I don't think Tasha would mind. Data: She was special to me. We were... intimate.
The episode "The Inner Light" is made of this trope, but it reaches an all-time high at the end when Picard discovers he still knows how to play Kamin's tune of the flute... sniff
Another instance is the spine-tingling moment when Batai and Eline, both long dead, come back to Picard at the end.
Tasha's Memorial Service. Could've been bad. Could've been really corny, but they pulled off with just the right tone, made especially poignant by Data's ultimate confusion about the whole thing, and Picard having to tell him that he hadn't missed the point at all.
The line that got Dummied Out gets extra points (you can tell where they cut it; they kept Geordi's eyebrow-raising reaction shot)
Tasha: Data? It did happen.
Also in the episode "Skin of Evil": When Deanna Troi responds automatically to the bitter and twisted Armus's question about how much she is willing to give to save her crew. Any member of the crew: Everything.
In the episode "Brothers":
Soong: Everybody dies, Data... Well. Almost everybody.
After Data tells Soong that he won't be able to grieve for him, Soong replies, "You will, Data. In your own way."
All throughout the episode, Data repeatedly calls Soong "sir." As Soong is about to die:
Soong: Goodbye, Data... Data: Goodbye... Father.
Meanwhile, Data's actions have stranded the Enterprise in orbit with a boy who has only a few days to get to a medical facility and a very irritated crew. They're understandably pissed off, but when they're preparing to beam down to the planet to look for him, Picard has only five words: "Bring him home, Number One." It's rather sweet that despite all the trouble he's caused, they're still determined to get him back where he belongs.
The ending of "Tin Man", when Data talks to Counselor Troi about the disappearance of her friend. There's a rare moment between the two of them (who are literally the most different characters in the show on an emotional level), when Data actually takes a step towards understanding emotions—not just defining them, or explaining how they affect people, but actually understanding. They end with Data turning to the viewscreen, in as close to contentment as he ever gets, and Deanna leaning against him. D'awww.
Troi: Data... you do understand. Data: Yes, counsellor. When Tin Man returned me to the ship, I realized... this is where I belong.
Speaking of Troi and Data, in "The Child" When Troi goes into labor, Data offers to stay with her. Dr. Pulaski objects, stating that she needs human contact and Data is nothing more than a machine. Troi defends him and personally asks him to stay with her while she gives birth.
Not to mention his expressions and voice afterward. When Data thanks Troi for allowing him to participate, he sounds so awed.
The final scene in "All Good Things", when Picard finally joins in on the crew's poker game.
Picard: I should have done this a long time ago.
Troi: You were always welcome.
From that same episode, Q actually congratulating Picard for figuring out the puzzle, and in the larger context, proving humanity was worthy to continue its trek through the stars. And Picard actually thanking Q.
Picard: Thank you. Q: For what? Picard: You had a helping hand in getting me out of this. Q: I was the one who got you into it, Jean-Luc. That was the directive from the Continuum. The part about a helping hand... was my idea.
Then you have this:
Picard: I sincerely hope that this is the last time that I find myself here.
Q: You just don't get it, do you, Jean-Luc? The trial never ends. We wanted to see if you had the ability to expand your mind and your horizons. And for one brief moment, you did.
Picard: When I realized the paradox.
Q: Exactly. For that one fraction of a second, you were open to options you had never considered. That is the exploration that awaits you. Not mapping stars and studying nebulae, but charting the unknown possibilities of existence.
Then, Picard asks Q what he's really saying, Q begins to tell him, changes his mind, smiles broadly and bids him farewell.
Q: In any case, I'll be watching. And if you're very lucky, I'll drop by to say hello from time to time. See you... out there!
To collapse the anomaly, the Enterprises from three different timelines have to enter it. Present and future are onboard, but Picard has to convince the past crew:
Picard: Now, this will put the ship at risk. Quite frankly, we may not survive. But I want you to believe that I'm doing this for a greater purpose and that what is at stake here is more than any of you can possibly imagine. I know you have your doubts about me... about each other... about the ship. All I can say is that although we have only been together for a short time, I know that you are the finest crew in the fleet and I would trust each of you with my life. So, I am asking you for a leap of faith... and to trust me."
The TNG episode "Time's Arrow" has one after the crew find evidence that Data is going to die (i.e. his severed head buried underground in San Francisco... it's a time paradox thing). The reaction of the crew is, in Deanna's words, like discovering someone you love has a terminal illness, and people start acting awkward around him (like stopping conversations whenever he enters a room). The fact that the entire crew, including the captain, spends the best part of the episode trying to keep him away from any incidents that might result in his predestined death is heartwarming enough, but by far the sweetest moment is when Data confronts Deanna and Will about their behavior and Riker quotes:
Riker: It's just that we've become accustomed to your sensory input patterns. Data: Ah... I am also fond of you, Commander. And you as well, Counselor.
Data's reaction is especially heartwarming: rather than trying to avoid his death with any sort of fear or apprehension, Data instead decides to keep living his life the same way he always has, and actually finds comfort in the fact that he has a finite existence because it brings him closer to being human.
This exchange in "Masks" (which would've been more heartwarming if it didn't immediately become disturbing, because Data sounding anything other than calm and composed, much less outright scared, is a freaking event):
Geordi: Hey you're gonna be all right. You're with me, okay? Data: ... Okay. [sic]
Riker: For an android with no feelings, he sure managed to evoke them in others.
The usually stoic and unyieldingly stubborn Worf accepts his son Alexander's help (indeed, asking for it) in relearning how to walk at the end of the episode "Ethics".
Say what you like, but Lwaxana Troi making friends with Alexander in "Cost of Living" was positively adorable.
Tasha discovering that Worf bet on her to win the Martial Arts tournament on the ship in "Skin of Evil". This seems like more of a "heh, sweet" moment (or an incredibly clumsy Ship Tease) than a Crowning Moment of Heartwarming, until you realize this is the episode where she dies and she won't be in the tournament. Then it becomes a bit of a Tear Jerker.
The fact that Tasha left a holo-message for her friends. Her time on the Enterprise was, effectively, her first time with a family who cared about her as an individual, and, knowing that she'd likely die in the line of duty, left them this message, presumably to give them a sense of closure that they couldn't have due to whatever death she would suffer.
Denise Crosby would say that had she been given more scenes like that, she might not have left so suddenly.
In "Hide and Q", Riker (gifted with Q powers) gives their blind navigator Geordi real sight for a few moments. The first thing Geordi does is turn to look at Tasha and delivers a line that would've been Narm if it came from anyone else.
Geordi: ...You're even more beautiful than I imagined.
The episode "Thine Own Self": When explaining to "Jayden" that her mother is dead, Gia draws on Data's buried memories of the Enterprise and her crew, despite the fact he currently has amnesia and has no idea who he is:
Gia: Father said she... she went to a beautiful place, where everything is peaceful, and everyone loves each other. Where no one ever gets sick. Do you think there is really a place like that? Data: [Looks up at the moon] ... Yes.
In the episode "Offspring", when Data creates a daughter named Lal (which means "Beloved" in Hindi), there are a few aaaww moments. One that really stands out is when Lal observes how humans hold hands to show affection. When she next sees her father, they just hold hands for a while.
This also happens at the end when Lal fails. Data says she "so enriched his life" that he couldn't just forget her and he took her memories and put them into his positronic brain, saying she'd always be with him.
And the dialogue, that, in another situation could have been Narm or just Tastes Like Diabetes, but instead gets the tears flowing.
Lal: I love you, father.
Data: I wish I could feel it with you.
Lal: I will feel it for both of us.
Don't forget Captain Picard's defiance of Admiral Haftel, who wants to take Lal away, without Data, refusing to refer to Lal as 'the android' because, like her father, she is still a sentient being who deserves to have choice in her fate - and she doesn't choose to leave her father. It's a vivid contrast to how he reacted to learning that Data had created Lal at the start.
In the episode "Inheritance", when Data meets Juliana, who is essentially his "mother", there's a touching Continuity Nod when she finds out about Lal, and exclaims, equal parts ecstatic and heartbroken, "I had a granddaughter?"
Admiral Haftel was an Obstructive BureaucratJerk Ass for most of the episode, but once Lal's neural net begins to fail, he immediately offers to assist Data in saving her, and is visibly moved by Data's heroic effort. By episode's end, he seems almost a completely different character.
In "The Wounded", where O'Brien beams over to the ship of his former captain, Maxwell, who has gone rogue and is trying to kill Cardassians, convinced that they are plotting another war against the Federation. Seeing O'Brien's pain at seeing his old captain acting this way, seeing the bitterness Maxwell holds for the Cardassians who had slaughtered his family in the previous war, seeing him slowly bring Maxwell back to reality, and Maxwell realizing how close he is to Jumping Off the Slippery Slope, all in very unexplicit ways, is touching enough, but when he remembers the old song mentioned earlier, and asks O'Brien for the words. He trails off at the end, letting O'Brien finish. The whole scene doubles as a Tear Jerker.
Maxwell: I'm not going to win this one, am I, Chief?
The two men singing The Minstrel Boy in honor of a fallen comrade is absolutely heartbreaking. The Minstrel Boy will be O'Brien's Leitmotif throughout the rest of the series and his entire run of DS9; symbolically showing O'Brien as an innocent man marked by the horrors of war.
This makes it even more touching that he was humming it at the breakfast table earlier in the episode.
"The Host" swung back and forth between this and Tear Jerker, but one of the sweetest scenes ever to hit Star Trek occurred between Beverly Crusher and Odan (hosted at that time in Will Riker's body). She'd walked away from her relationship with Odan because she couldn't deal with him essentially living inside someone she'd basically considered a brother for years, but when she sees the rosebud Odan gave her blooming on her desk, she goes to where he's staying, resulting in this sexy, adorable dialogue (which also proved that Gates McFadden and Jonathan Frakes could fucking act):
Beverly: [on how to administer anti-rejection medications in the middle of diplomatic talks] You could schedule a series of breaks...
Riker-as-Odan: Oh, that's good. ...Beverly. I want you. If you're going to leave you had better leave right now.
Beverly: [choking back tears] I'm not leaving!!!
In the same episode, Deanna tells Beverly about the first man she, and any woman alive, ever loved: her father. Heart-warming and heart-breaking in the same instant.
Deanna: What I wouldn't give to hear those songs again.
"The Bonding." Jeremy Aster is still shellshocked by the death of his mother. Cue Captain Picard (who is not comfortable with kids) reassuring him with a line that always gets to me:
Picard: Jeremy, on the starship Enterprise, no one is alone. No one.
At the end of the episode "Deja Q", Q's expressions of gratitude to the crew of the Enterprise were mostly viewed as annoyances, but Q giving Data a moment of laughter was purely heartwarming.
The end of "Data's Day" is quite uplifting, but Captain Picard visiting the newborn baby and telling him "Welcome aboard" is wonderful.
The episode "Family" tugged on the heartstrings a bit, but the defining moment was the conclusion, in which Captain Picard gets into a fight with his brother over past differences, breaks down about his experience with the Borg (which was essentially a Mind Rape and he hadn't come to terms with it yet), and then they get crazy drunk together, finally reconciled after decades.
In another subplot of the same episode, Guinan tells Worf's adoptive parents that when he looks towards home, he's looking not to the Klingon Empire but to them.
At the end of the episode "Disaster", the three children Picard shepherded through danger by deputizing them as officers present him with an adorable plaque. The real CMOH comes when he gives the bridge to "Number One" and both Riker and the eldest girl respond.
Several in the episode (not the movie) "First Contact" between Picard and Chancellor Durken, who woke up that morning as the leader of a whole world and now finds that his world is only one of many - "But I think it was a good day." Later, when he learns that the Federation has been covertly observing his people, Durken angrily confronts Picard... and then admits he might have made a similar error in Picard's place, and it actually pleases him to know the aliens are not infallible. Picard wryly observes that he has made "some fine [mistakes] in my time."
At the end of the episode "The Chase" the assembled alien races have left in a huff, refusing to believe that they could possibly share a common ancestor. Picard is feeling down, but then the Romulan commander calls him and offers a little hope. "It would seem that we are not completely dissimilar after all; in our hopes, or in our fears." Picard can only answer "Yes..." The Romulan puts forth an olive branch, of sorts. "Well then perhaps, one day..." Picard is comforted, and echoes: "...one day..."
This doubles, perhaps, as a Call Back to the end of "The Defector", and an affirmation of Picard's hope there that "one day" they might be able to deliver Jarok's final message to his daughter, and explain why he sacrificed everything in the belief that he was preventing another war, and giving her a chance to grow up.
Picard smiling and laughing when he gets stabbed in the heart at the end of "Tapestry", knowing his life would be restored to its normal timeline.
Even more heartwarming was the closing scene between Picard and Riker, and the tears start flowing when Picard begins to reveal his other adventures to Riker.
Picard: I still don't know what to make of it. Was it a dream? Was it one of Q's elaborate tricks?
Riker: A lot of people near death have talked about strange experiences, but I've never heard one so detailed.
Picard: And, you know, there's still a part of me that cannot accept that Q would give me a second chance, or that he would demonstrate so much compassion. And if it was Q, I owe him a debt of gratitude.
Riker: In what sense? It sounds like he put you through hell.
Picard: There are many parts of my youth that I'm not proud of. There were loose threads, untidy parts of me that I would like to remove. But when I pulled on one of those threads, it unraveled the tapestry of my life.
Riker:(smiling) I was just trying to imagine a hell-bent-for-leather young officer insulting a Nausicaan twice his size. I wish I'd had a chance to know that Jean-Luc Picard.
Picard: Oh, well, to tell the truth, that wasn't the first run-in I'd had with a couple of surly Nausicaans.
Picard: Oh, yes. During my sophomore year, I was assigned to training on Morikin Seven. Well, there was a Nausicaan outpost on one of the outlying asteroids, and one day... (Credits Roll)
"Clues": Data would rather suffer a court martial and be "stripped down to [his] wires" to find out why he's lying to Captain Picard than endanger the lives of the crew.
The look on Spock's face when he mind-melds with Picard at the end of "Unification." He FINALLY realizes his father, Sarek, loved and respected him for all these years and gets the closure that he needed.
Also, from the same episode:
Spock: In your own way, you remind me of another captain of the Enterprise I once knew.
"Hero Worship": Pretty much all of Data's interactions with Timothy, the ten year old boy who was the sole survivor of his destroyed vessel. He pretty much latches onto Data, going so far as wanting to be an android himself. This is also partly Tear Jerker as the real reason he wants to be an android is because they don't have emotions, and ergo, he wouldn't have to feel the grief and guilt of losing his entire family.
We all knew Data's relationship with Jenna D'Sora from "In Theory" was doomed to failure but their obvious friendship, and the fact that he tries so damned hard to make it work (he asks pretty much the entire bridge crew for advice because he has no idea what he's doing) is as endearing as it is funny.
At the closing of "Sarek", Picard's exchange with Sarek's wife.
Picard: He loves you, very much.
Perrin: (smiles) I know. I've always known.
In "The Next Phase," Geordi and Ro are presumably killed during a transporter accident. They're only "out of phase," but none of the crew can see or hear them, so a memorial service is planned. Data is the one to plan it, and he recalls that Geordi was the first person to treat him as more than a machine.
Data: I never knew what a friend was, until I met Geordi. He spoke to me as though I were human. He treated me no differently from anyone else. He accepted me for what I am. And that - I have learned - is friendship."
All the more effective—to the point of Tear Jerker—when you consider the math: before his assignment to the Enterprise, Data had been alive for twenty-six years, nineteen as a Starfleet officer, and in all that time not one person accepted or befriended him. Thank god for Geordi.
And earlier in the episode, when Data calls Geordi his "best friend" and asks to plan the memorial service. Despite Data being technically unable to grieve, nobody has a problem with him running the funeral.
Picard's intended eulogy for Geordi:
Picard: I've been thinking about the first time I met Geordi La Forge. He was a young officer, assigned to pilot me on an inspection tour, and I made some off-hand remark about the shuttle's engine efficiency not being what it should. And the next morning I found that he'd stayed up all night, re-fitting the fusion initiators. Well, I knew then that I wanted to have him with me on my next command."
Ro's parting words to Picard:
Ro: I just wanted to say thank you for trusting in me when no one else would.
In "Sins of the Father," Worf explains his situation and asks Picard for a leave of absence. Picard refuses.
Picard: If I understand correctly, a Starfleet officer, a respected member of my crew, could be charged with a capital crime. Your actions on this matter will reflect on this ship, and on the Federation. Therefore, it seems only appropriate that your captain should be at your side while you make your challenge. I'm sure you would do no less for me."
After his brother is stabbed, Worf chooses Picard as his cha'Dich.
Picard: Thank you, Lieutenant. I appreciate the gesture, but I know there are stronger and younger men from whom to choose. Worf: I can think of no one I would rather have at my side.
Worf accepting discommendation to protect his brother, and the Empire.
In "Parallels," Worf keeps shifting into different realities. The last one is fairly idyllic - he's first officer, married to Troi and has two children with her. However, in this reality, Alexander doesn't exist and such a thought saddens Worf.
In that reality, the events of "The Best of Both Worlds" ended instead with Picard dying and Riker staying captain. When events allow for this alternate Riker to speak to our Picard, he tells him, "It's good to see you again, Captain. It's been a long time."
Geordi refusing to accept Data's apparent "death" in "The Most Toys", going over every scrap of evidence repeatedly looking for some proof that his friend is still alive. Finally, he remembers the audio logs and discovers something that Data would never have done (specifically, not announcing his departure from Fajo's ship) and uses that to build an argument that Data is alive.
Basically everything between Picard and Wesley in "Final Mission".
The ending of "Who Watches the Watchers" is very heart-warming. And tear-jerking. And so awesome.
Nuria: You have taught us there is nothing beyond our reach.
Picard: Not even the stars.
Nuria: Pahkee. [Pahkee, a little boy, gives Picard a piece of woven cloth] I wish you good journeys, Picard. Remember my people.
Picard keeps the cloth and uses it as a cover for the headrest on the chair in his ready room, a place it will keep on the Enterprise-E up until it disappears in Nemesis. The director never watched an episode and unfortunately disregarded things of significance and continuity.
This is a small moment, but when Picard helps Beverly up from the floor after she crashes through the vortex in "Remember Me", Beverly hugs him. A proper squeeze, too, and Captain "no-touching-I-am-dignified" Picard squeezes back. It was the only time they hugged while not possessed / drunk / impersonated by aliens.
The Beverly-Picard scenes in "Attached". Every single one of them.
The sheer fact that they gave Data clothes to wear. Granted it was because "some of the colonists objected to having of an anatomically correct android" running around naked. But look at it this way, they did it out of decency which is also a human behavior. Something which Data holds in high regard and fascination.
The moment in "New Ground" which introduces Alexander when Riker and Worf save the endangered animals. It wasn't anything big, it wasn't anything anyone would have noticed...they did it because a little boy begged them not to leave them behind.
Picard in "All Good Things" while trying to get things organized with the Enterprise crew from the past, has a brief chat with O'Brien. That smile in this voice when he mentions their common interest in model ships (something that O'Brien won't mention for another few years), and the smile on his face when O'Brien looks up at him confused is just adorable.
In one of the licensed novels ("Spartacus"), the Enterprise encounters a ship full of androids who look and act far more human than Data, which causes him to experience a small existential crisis (because they've managed to develop individual personalities, something that he fears lies the scope of his own programming.) Geordi shows up and gives him a Tough Love pep talk to the effect that he always tries too hard to be human, but the crew will always care for and respect him even if he never fully grasps human behavior. At the end, Data gives Geordi one of his rare smiles and says, "You are my best friend." D'aww...
A sort of general one, but the way that Geordi often touches Data (up to and including holding his hand) is very sweet. It's not only a testament to the strength of their friendship, but to how Geordi sees Data — as a person, not a machine.
In "Where No One Has Gone Before," the Enterprise is accidentally sent to a very distant region of the universe, where anything the crew can think of becomes real. Picard briefly encounters his Missing Mom, who offers to pour him some tea and have a nice talk.
Picard: This can't be. You've been— Yvette: Dead? But I'm always with you. You know that. Picard: Yes. I felt that.