Found this one here that works for me...
(website on secure server so replaced it here with "x")

Just need to ammend to:
Paste to next available row or next sheet if there is no more space
available.


Sub test()
Dim ipstring As String
Application.DisplayAlerts = False
ipstring = "x"

With ActiveSheet.QueryTables.Add(Connection:="URL;" _
& ipstring, Destination:=Range("a1"))
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range(Range("a1"), Range("a1").End(xlDown)).Select

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Columns.AutoFit
Range("a1").Select

Application.DisplayAlerts = True
End Sub