Epic is where I believe all these super heavies, knights, and titans should have stayed. This is where I first encountered them and this is where I still enjoy using them. The scale-up to 40K of such units simply does not work very well.
Well depends, an entire army of knights is yeah not really good for anything other than a campaign setting. A single wraithknight sized thing does seem to be ok at 2k points, but doesn't seem to make sense for 1.5K engagements, considering how rare these things are meant to be etc. That said Lore and game play haven't really lined up very well for a long time.
Actually Titans only makes sense in Apoc.
Super-heavy tanks are in a bit of a strange spot, too, something like a Lynx is physically too big for small engagements, even in a 2k point game, it takes up too much real-estate with the amount of things on the board these days.