I found I had a lot more trouble with this when I thought about it in terms of work and motivation. There were a lot of days back then when I wasn't "feeling it" or I'd start feeling guilty because I wasn't painting.
Now I don't have that issue, but it wasn't really a secret formula or anything. But in case it should be of some help to you, here's how I solved the issue permanently for myself:
1. Enjoy the process. And I mean this on the most primal, lizard-brain level you can manage. Just enjoy the basic satisfaction of smearing paint on things. If you can remember this joy, one we all knew once, no task will ever defeat you again no matter how godlessly boring it might be.
2. Why are you doing this? This is a serious question. I bought all this crap and all these figures for some reason, so what was it? Was it for fun? If so, then it's easy. Paint something fun. But that's also a dirty trick, because even the fun ones have unfun parts to them. So you can't count on or wait for motivation. So why else did I bother with it? It can only be because I wanted to get something done. Very good, that makes it an easy decision to just do it so I do. Right now I have about 35-40, depending on how you count, British Infantry to paint. I'm doing this because I want to play with them. There will never come a day when, fired up by the rays of sunrise and a cup of strong coffee that I'm going to exult: "YESSSS! Today I get to paint 40 pairs of brown trousers!" But, those trousers gotta be done. So I will sit down and do them, if it's important for me to get them done. There's no other way to escape that fate, no matter how wretched said fate might be. So I reset my brain to #1 above, and paint brown trousers until there are no more brown trousers to paint. Then I start doing boots.
3. It's work, it's fun, but most of all it's a hierarchy of wants. If I want to do something, I will therefore do it. If I don't want to do it, then there must exist a more compelling reason (want) in order for me to do it - in the case of the British Infantry, I want to play with them. If I want to do that more than I want to paint them, I will still paint them. But if I don't want to paint them and my want to play with them is not strong enough, I go and play Minecraft instead. My want to trade carrots to villagers for emeralds is greater. If it is, I never feel guilty about it. I have decided that the price of delayed deployment on the grass mat is a fair one to pay for some vidya games. If there's any question or doubt of that, then it means I probably want to get them done more and so I should sit down and do that instead. (this is a basic law of happiness, if I'm going to feel bad and guilty for playing minecraft then I'd clearly be happier slogging through trousers instead). Don't make yourself feel bad for no reason!
4. It's more satisfying than exciting. Our hobby is sitting down, often alone, for long periods of time doing repetitive tasks. This matches no definition of exciting. It is, by nature, dead boring. But it's also something we find compelling or we wouldn't be here. That's because the reward at the end is satisfaction in getting things done and slowly watching a shelf of completed items grow. If you remember this, then it follows that if you make the time to sit down, turn your brain to #1, and do it then things will finish of their own accord eventually and your satisfaction will increase. If you're having trouble finishing it's because your reward expectation is off - you're expecting the immediate task to give satisfaction. If you get stuck there then you don't finish, and so you cheat yourself out of the satisfaction payoff in the end. This deadly trap ensnares many, it's why there are shelves of shame. But remember that IT IS A TRAP! You will get mostly there, lose moitivation, and look for an exciting new thing. Often you might do the same when the charm of that piece wears thin. Enough repetitions of it and you'll be left wholly unsatisfied. Don't fall into the trap. It's not about motivation, it's about completion. Keep going, keep going, keep going. Do that often enough and you'll retrain your expectations of satisfaction and it will never even occur to you to abandon something.
So: Figure out the simplest enjoyment you can get from it and remember that's your core motivation. Remember why you bothered doing it to start with. Be comfortable and aware of your hierarchy of wants and how they conflict. When in doubt, sit down and do it anyway because it does have a guaranteed payoff in the end. If you do all of this then I can pretty much give you my 100% pure guarantee that you will never feel burnout or dead motivation again, and your shelf of finished items will bloom.
Edit to add: I should also note that there is never going to be a circumstance where painting a platoon (or more) of infantry (or orcs) will be anything except exhausting. It's baked-in as an essential nutrient. Doing my first platoon of German Grenadiers had my brain goo leaking out of my ears. But now there's a whole big whack of them on my shelf and that, I gotta say, feels pretty damn nice. And somehow I found myself buying another box of them, so I can do it all over again. It's still probably going to cost me some leaky brain goo to get through another batch, but it did pay off enough for me to re-up for another tour. I consider that proof of my above method.