Sub FixedData()
Sheets("Data").Select

Dim X1, X2 As Long
'Last Letter Counter
Dim LCount1, LCount2 As String
'Fixed Value- Last Letter
Dim Len1, Len2 As Long

With ActiveSheet.UsedRange
X1 = 2
X2 = .Cells(.Cells.Count).Row

'--Fixed value for Name
For X1 = 2 To X2
    'Adding extra space-sign for InStrRev
    .Cells(X1, 2) = .Cells(X1, 2) + Space(1)
    'Last letter counter
    LCount1 = InStrRev(.Cells(X1, 2), " ")
    'Substraction result
    Len1 = 50 - LCount1
    'Fixed value by Space
    .Cells(X1, 2) = .Cells(X1, 2) + Space(Len1)

'--Fixed value for Alias
    .Cells(X1, 4) = .Cells(X1, 4) + Space(1)
    LCount2 = InStrRev(.Cells(X1, 4), " ")
    Len2 = 30 - LCount1
    .Cells(X1, 4) = .Cells(X1, 4) + Space(Len2)
Next X1
End With

End Sub

Sub SavingFixedValuesFile(FullFileName As String, _
StartRow As Long, LinesCounter As Long)

Dim LineWriter As String
Dim RowIndex, LastRow As Long
'Dim LinesCounter As Long
Dim FNum As Integer

Sheets("Data").Select
LastRow = Sheets("Data").UsedRange.Rows.Count

If LastRow < 1 Then
MsgBox "No data was found, Worksheet 'Data' is empty", _
vbCritical, "Rows Check"
Exit Sub
End If

FNum = FreeFile
Open FullFileName For Output As FNum

For RowIndex = StartRow To StartRow + LinesCounter
    LineWriter = UCase(Cells(RowIndex, 2)) & "|" & _
                        Cells(RowIndex, 4) & "|" & _
                        Trim(UCase(Cells(RowIndex, 6)))
                        
    LineWriter = RTrim(LineWriter)
    Print #FNum, LineWriter
Next RowIndex
Close #FNum

End Sub

Sub ExportFiles()
Sheets("Data").Select

Dim RecNum, RecNumPerFile As Long
Dim EndOfFile As Long
Dim FileNum, FileCounter As Long
Dim FileDat As String

RecNumPerFile = 10
RecNum = Sheets("Data").UsedRange.Rows.Count
FileCounter = _
WorksheetFunction.RoundUp(RecNum / RecNumPerFile, 0)
FileDat = "_" & _
Mid(Left(Excel.Application.UserLibraryPath, 16), 10, 7) _
+ Format(Now(), "_yyyymmdd_hhmmss")

MsgBox "Generating " & FileCounter & " files...", _
vbInformation, "Export Files"

For FileNum = 1 To FileCounter

'First File (from Row = 2)
If FileNum = 1 Then
SavingFixedValuesFile FullFileName:=ThisWorkbook.Path & _
"\FileNo" & FileNum & FileDat & ".txt", _
StartRow:=2, LinesCounter:=8

'Last File
ElseIf FileNum = FileCounter Then
EndOfFile = RecNum - (RecNum - 1) * 10
SavingFixedValuesFile FullFileName:=ThisWorkbook.Path & _
"\FileNo" & FileCounter & FileDat & ".txt", _
StartRow:=((FileCounter - 1) * 10) + 1, _
LinesCounter:=EndOfFile - 1

'Next
Else:
SavingFixedValuesFile FullFileName:=ThisWorkbook.Path & _
"\FileNo" & FileNum & FileDat & ".txt", _
StartRow:=((FileNum - 1) * 10) + 1, _
LinesCounter:=9
End If

Next FileNum

End Sub