Here's a nicer routine that looks at just the chart series without assuming anything about the data in the sheet. It starts with the series formula, splits it into an array, reverses the X and Y elements of the array, then rejoins the elements into a modified formula, and reapplies the new formula.

```
Sub SwitchXY()
If Not ActiveChart Is Nothing Then
Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
Dim SrsFmla As String
SrsFmla = srs.Formula
'' =SERIES(name,xvalues,yvalues,number)
Dim vFmla As Variant
vFmla = Split(SrsFmla, ",")
'' array {"=SERIES(name","xvalues","yvalues","number)"}
Dim temp As String
temp = vFmla(LBound(vFmla) + 1) ' xvalues
vFmla(LBound(vFmla) + 1) = vFmla(LBound(vFmla) + 2)
vFmla(LBound(vFmla) + 2) = temp
'' array {"=SERIES(name","former yvalues","former xvalues","number)"}
SrsFmla = Join(vFmla, ",")
'' =SERIES(name,former yvalues,former xvalues,number)
srs.Formula = SrsFmla
Next
End If
End Sub
```

Below is some dummy data, an XY chart made from the data with X and Y as Excel plotted them, and the same chart after running the code.

**Update:**

I've used this question & answer as inspiration, and added some better code to account for more complicated series formulas, in a new tutorial on my blog, Switch X and Y Values in a Scatter Chart