Is he being offered a company transfer, or an entirely new job? If it's the former, you'll probably be on an L visa derivitive, which will allow you to apply for a work permit when you arrive (only valid for the length of DH's visa), which at least keeps your options open. If it's the latter, you may get stuck on the H4, which means you can't work at all.
Even if you plan, in advance, not to work, it's still miserable to be stuck on the H4, aka visa from hell: you have no options, you're entirely dependent, and you even have to be careful about voluntary work (you can't do for free any tasks that people would normally be paid for).
If you found maternity leave boring, I would really question whether it's a good idea to be a trailing spouse. US corporate life is even more intense (IMO) than in the UK, so you'll be landed with all home admin and childcare, and it sets a relationship pattern that's actually quite hard to snap out of after a few years.
There's also the possibility that your DH will love his job and be offered further advancement there, and want to stay longer, in which case you may have issues -- I think a lot of trailing spouses start off thinking 'it's only a few years', since most relocations involve limited-period visas, but often things go well, green card sponsorship is offered, and there are difficult decisions later down the line.
I really like New York, but you sound so absolutely positive and happy about your current situation (love your job, neighbourhood, family, work/life balance) that it makes me want to say noooo, don't do it!
(Perhaps I've relocated too often, but I just don't buy the 'you'll regret not trying it' line any more. I've met plenty of expats who've regretted their decisions -- and being a trailing spouse, in particular, can really limit your option; perhaps not immediately, but increasingly so as the years pass. If you've got a good thing going here, that's worth a lot.)