Do writers ever get tired of their own novels and never want to look at them again after they’ve been written? How do they find the motivation for revisions, rewrites, etc.?

You find the motivation for revisions (hopefully not for a complete rewrite) because you want to finish the darn thing. If you don’t, you may have wasted all that time and energy. Besides, I want to pack it off to my editor so it can begin the journey to publication. My tenth novel is waiting patiently on my computer for a final re-read in three weeks. If there are any, they’ll be minor, I’ll fix them, make sure the ms. is formatted correctly, and send it off.