I have two favorite moments.
First and foremost, at the end of Generations just after Picard and Riker beam up from the totaled Enterprise D. Right when the music cues and seeing the ships fly off..I'm not sure why, but the first time I saw that it brought tears to my eyes.
Second favorite moment is in Voyager. The episode where Torik tries to mate with Bellana. At the very end when they discover the Borg body in the ruins. The first time I saw that it gave me the most intense chills down my spine I've ever felt. I think it gave a certain anticipation from that episode to Scorpion that can hardly be matched. (Exceptions include the season finale of The Best of Both Worlds: Part 1. I almost DIED.)