vba to generate XML file with different values

[AA_20069]

Assuming your data is in A like so:

Mark	Richads	Jeff
Peter	Simon	Blue
Carol	Jane	Mary
Jack	Peters	Paul

Sub GenerateXMLFiles()
    Dim ocell As Range
    Dim sXML As String
    Dim lFile As Long
    Dim lCount As Long
    For Each ocell In ActiveSheet.UsedRange.Columns(1).Cells
        lCount = lCount + 1
        sXML = "<?xml version=""1.0""?>"
        sXML = sXML & vbNewLine & "<Name>" & vbNewLine & "<Firstname>" & ocell.Value & "</Firstname>"
        sXML = sXML & vbNewLine & "<Surname>" & ocell.Offset(, 1).Value & "</Surname>"
        sXML = sXML & vbNewLine & "<Middlename>" & ocell.Offset(, 2).Value & "</Middlename>"
        sXML = sXML & vbNewLine & "</Name>"
        lFile = FreeFile
        Open "xml file " & lCount & ".xml" For Output As lFile
        Print #lFile, sXML
        Close lFile
    Next
End Sub

Thanks