Hm.
Should he be sulking and snappy because he's not had sex? No, that's stupid.
Should you be telling him to 'grow up' and 'get over himself' as though wanting to have a sexual relationship with his wife is purely his problem? No, I also think that's an issue.
It seems it's been framed in your relationship (by both of you) as 'DH gets sex, DW allows it' and that the having or not having of sex is therefore his issue and you just magnaminously allow it sometimes. When really the issue between you both is that you do not, for whatever reason, want to take part in this thing that affects you both very often.
So why is that?
Is the household split of labour equal - does he do as much childcare, cleaning, organising, remembering you need washing up liquid, buying new school shoes, sorting insurance, gardening? If not, there's step one - tell him you're tired and you're not going to get less tired unless he steps up to be an equal partner. Tackle the problem of your tiredness together by creating an equal split.
If you're physically exhausted beyond the norm, then have a look at that. Improve your diet, buy in help if you can afford it to free up time or work out what's not a priority if not, exercise, go to the doctor's if you're really concerningly tired all the time.
If you're 'just' tired by the general drag of family life with small children* and he's a good, equal partner the rest of the time and around the house, then I could see an argument for you making more time for sex - scheduling it in (even just in your own head) for some time to have a bath, make yourself feel nice, get yourself in the headspace of wanting to be intimate with him - I feel myself feeling irritable and less connected to my H if we haven't had sex in a while and I always feel so close and connected afterwards, so even if I don't feel absolutely gagging for it I try and make time because I appreciate that time together.
Amongst ALL of that, though - speak to him properly, instead of just telling him to grow up, because although he's doing it horrendously he is communicating something to you and I think opening the conversation a bit more broadly might help. Tell him that sulking and snapping at you doesn't make you feel like you want to have sex, and it adds guilt and stress and annoyance to something that's supposed to be fun. And then talk about the reasons why you don't want to, and what you can do about it together.
I think this'll be an unpopular approach - there'll be lots of people saying "oh GRIM I would divorce him immediately for snapping and sulking" and that's up to them. But in an otherwise good relationship (and knowing that I get the same irritable feeling when I don't feel as connected to my husband, although I am good at hiding it!) I'd be more inclined to actually communicate with him about it, while also reminding him that that does nothing for your desire to have sex.
*I know that it's exhausting, I mean within the usual limits and not medically worrying tiredness