Surprised this hasn't been mentioned yet but a couple of the Enterprise scenes in the new movie: rising from the gases of IO to
intercept Neo's vessel and also later intercepting his missiles heading for Spock in the future-ship
The fleet warping out to battle earlier in the film is hella-cool too.
As is the final "Space, the final frontier" montage.
Hell, the whole movie is one giant "Hell-yeah" tbh
In DS9, I always loved the whole exchange between Dax and Kira about warping inside the Bajoran system to intercept the founders
plan to blow up the Bajoran Sun:
"We can't reach it in time"
"you wanna bet? Take us to warp!"
"INSIDE a Solar System"
"If we DON'T there WON'T be a SOLAR SYSTEM left!"
Also, the reveal about Bashir being a changeling in that ep (or was it the episode before? ) was pretty Heck Yeah too
(Ah, the days before spoilers! Good times!)
One of the best though: TNG Cause and Effect.
The TEASER featured the Enterprise-D exploding. Heck YEAH also crossed with "Well, now what, we gonna watch 40 mins of black screen from now on?