Conceptually, yes, the CIWS could have been effective against the missile the struck the Moskva.
The US had a similar incident, back in '87, with the frigate USS Stark. It was struck by two Exocet missiles launched by an Iraqi fighter during the Iran-Iraq war. The Stark was in international waters, and the attack was unprovoked. Bluntly, the ship was not expecting the attack, and was unprepared for it. While it was equipped with the CIWS, it was in standby mode. Also, the Stark mounted a single unit in the center of the ship, facing aft. As I recall the missiles actually struck the forward quarter of the ship. Even if the CIWS was enabled, I believe they would have had to turn the ship for it to engage the missiles.
As for the Moskva, let me qualify I have no support whatsoever for what Russia is doing. That said, I do have a base level of respect for fighting forces, and the people who staff them and stand the line.
I was shocked to hear about the Moskva. There's no reason those missiles should have struck that ship. That ship is particularly designed, much like our cruisers, to defeat threats such as those missiles. For the US Navy, a prime role for our cruisers is to defend not just themselves, but a fleet role. In simple terms, the cruisers are there to protect the carriers, and are bristling with systems and sensors designed for that task from several threats.
The Moskva was similar to that role. The CIWS is the "last resort". The CIWS is for when missiles penetrate the other layers of defenses in place, including things like the combat air patrol, air-to-air missiles, etc. The CIWS is a "last mile" defense.
The Moskva had layers as well. It should have taken several missiles to penetrate what the Moskva could have utilized.
Obviously, being in the West, I do not hear flattering things about the Russian forces, their state of readiness, training, morale, etc., etc. Based on that knowledge, and the fact that the Moskva was lost, it's clear to me that that ship should not have been where it was. Here was a high level ship of war, in a war zone, not as a spectator, but as a participant, and it was not ready for that attack. That ship and her crew were not qualified to be there, not prepared to be there, and those service people deserved better.
The Russian navy failed that crew, and they should not have been in harms way like that. The Ukrainian attack should have failed utterly. It shouldn't have even been close.
The Stark was very embarrassing, and there are many critics of the modern US Navy. The Stark, however, was completely surprised, they weren't a combatant, they weren't posturing, the Stark was in the area.
The Moskva should not have been surprised. They clearly did not anticipate the attack (as I understand it, everyone was surprised the Ukrainians had this capability), but it's not really clear if the ship was prepared and capable even if they did. There's stories that even after it was struck, the crew did not perform well and contributed to the loss of the ship.