Look out for the pros that you enjoyed watching and check if they had any recent footage, you'd be surprised how many of them are still ripping. Reynolds, Spanky, Kalis, P-Rod just to name a few have been dropping parts semi-regularly.
Sometimes a video with minimal skateboarding gets me hyped to skate, the Quartersnacks Favorite Spots series is a good one:
Chanced upon an old episode of Thrasher's Out There with Ellington, minimal skateboarding but hearing him turn his life around after a close call with a long prison sentence was the best thing:
Edit: anything Ishod is good too, I actually prefer the Rough Cuts over the main release.
Edit edit: For compilations Memory Screen and Manolo Tapes do a fantastic job of splicing the best bits of a skater's careers with choice music selection
One of his best work