It would create the new worksheets, raise no errors whatsoever, but show nothing!
Nothing was changed in the spreadsheet. I even retrieved a copy from my backups.
There is no explanation I can think of for this but here is how I fixed it.
I opened the print preview dialog, went to page settings, changed the paper backwards and forwards, toggled a few other settings, left everything the way it was, closed the dialog and voila! it now works!