Whilst I really like the S5 Pro for it's apparent dynamic range (with that 'twin' sensor), I wouldn't necessarily think of it as a macro camera (but then what manfacturer is?!). Seeing as it's a Nikon D200 in all respects (aside from sensor/software) you have the extensive Nikon lens range from which to choose, as well as 3rd party (I use a Sigma 105mm macro lens, for instance).
Be interested in what anyone else suggests, Paul, especially as I don't know the S5 beyond handling it at Focus!