large video site - is drupal suitable?

does drupal provide modules which will create a set of new pages for each user (blog, about, photos etc)?

Yes. You may want to consider the Advanced Profile Kit module, which pairs up with either the Bio module or the Node Profile module to create one or more nodes for each user (Bio only makes one node per user and is simpler to understand, Node Profile can make multiple nodes per user as well as different nodes for users depending on what group/role they are a member of, and is more complex). It puts them all together very nicely for you using the Panels 2 module, and can also automatically draw in other various user data depending on a number of optional modules being installed. Here's a helpful comparison article on choosing the best profiles-as-nodes option for your needs. You can create per-user photo galleries using tools such as CCK and Views (an argument for the user name/ID on the view can display a given user's gallery).

is there a way to embed various flash apps into each users page automatically?

There are a number of video options for Drupal. Some of my favorites are Mediafield + Mediafield Display or the Media Mover module. I've also used FlashVideo module with success. There's also the old-fashioned Video module, which is a mega module with lots of functionality though less flexibility (most Drupal modules are moving away from this kind of all-in-one approach). I'm not experienced with integrating other video sources with Drupal outside of using video-related modules, but I have seen it done on various sites and according to a quote I heard by Earl Miles (Views module developer) in a recent interview on the Lullabot podcast, it is evidently not difficult to do. Here's one such example: http://myplay.com

I dont want lots of pesky tables appearing from the cms?

Drupal outputs standards-compliant XHTML and CSS, and none of core (to my knowledge) uses tables incorrectly. Your choice of contributed themes and modules however might use tables in its layout or added functionality, so check the code. Views module can produce tabular data as needed (tables aren't bad when used for their intended purpose).

Can I design my site myself and then just use Drupal to create the content for specific pages (or divs etc)

Absolutely. Drupal has an extensive and powerful theming system. Depending on the needs of a project I've made themes from the ground up, or using a base theme like Zen as a starting point to customize. Check out this free PDF chapter on Drupal theming from the "Pro Drupal Development" book. Also check out this post I made on my site with many Drupal learning resources. You can make Drupal look like just about anything you want, sky's the limit :) The theming in Drupal 6 versus 5 is even more powerful as well as easier (and augmented by the Firebug-like theming tool in the Devel module which you can download). I don't have experience using Drupal as "selectively" as it sounds like you might be considering (e.g. just calling in Drupal's functionality on certain pages of your own custom site)... I'd suggest you use Drupal to power the site as a whole, as it's quite competent for that, and embed your custom code into the flexible template files or directly into nodes and blocks as needed. However, if you do want to go the other route, I've read that by adding only a few simple lines to the top of your own files, Drupal can be bootstrapped and its full functionality is then available.

Hope this info helps. Good luck!

- David