I wrote a tutorial about this a long while back, so I am going to post it here.
http://rpgmaker.net/tutorials/444/

--------------------
Animated Tileset
--------------------

I just learned about this yesterday on March 13th, 2011, after seeing it in a RPG Maker 2003 project called 'Balmung Cycle'.

Here's what you do:

Make duplicates (for any number of frames the tiles are going to be) of the same tile set. Name them in order, and in each tile set, change the frame of the tile(s).

Say you have an auto tile of water with 8 frames. You would need 8 of the same tile set, and you would change the frame of the auto tile 8 times in each tile set.

To activate this, all you do is make a parallel process event on the map that's going to have the animated tiles. In Page 3 of the event options, select 'Change Map Tileset'. Change the tile set multiple times (to how ever many frames its going to be.) and put a 'Wait' after each tile set change. (i suggest using 0.2 wait.), and that's all there is to it.

If you're going to have tiles that have more frames than others, then what you would do is, for example, say you have water that has 8 frames, and grass that waves in the wind with 4 frames. You would have 8 tile sets, the water would change a frame in the 8 tile sets, and the grass would repeat frames 1-4 in the next 4 tile sets. Simple?

Example:

Water- 1, 2, 3, 4, 5, 6, 7, 8
Grass- 1, 2, 3, 4, 1, 2, 3, 4 (or) 1, 2, 3, 4, 4, 3, 2, 1 (or in any order you want)

If you're going to have tiles that move slower/faster than others, then what you would do is, for example, say you have the water with 8 frames, and you want the grass with 4 frames to move 2 frames slower than the water. You would have 8 tile sets. The water would repeat frames 1-8 in the next 8 frames, and each frame for the grass would remain the same for every 2 tile sets.

Example:

Water- 1, 2, 3, 4, 5, 6, 7, 8
Grass - 1, 1, 2, 2, 3, 3, 4, 4

Calculate the frames, and the delay of your tiles, and set them up accordingly. It's a lot of work, but it's worth it if you want really nice maps, but don't go crazy and have tiles with 50 frames XD!!! I don't know if that would slow the game's engine down.

---This concludes the tutorial---

Here's a video I made showing this method, enjoy!

[[Video No Longer Avaible]]
(NOTE, this video does not show you how this is done, it simply shows the tiles animating)

I used 2 auto tiles in this video. Shallow and Deep water. Each have 15 frames, and they animate at the speed of 0.2 seconds. You can animate more than just auto tiles. Animate anything you want.

~Now you can have maps as good in games like 'Secret of Mana'!!!~

PS: This method probably works in RPG Maker XP/VX as well. Give it a try and see.

I think this is nice, great job!

Thanks man!