Hi SUJAYS,

Refering the below articles i have created the example.

Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net

Export Windows Forms DataGridView to Excel using C# and VB.Net

Check this example. Now please take its reference and correct your code.

Form Design

In Windows Form i have taken a Button for Generate excel and OpenFileDialog to select file.

Namespaces

C#

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using ClosedXML.Excel;

VB.Net

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports ClosedXML.Excel

Code

C#

private void btnGenerateExcel_Click(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}

private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
    DataTable dt = GetDataTableFromExcel();
    List<DataTable> dts = dt.AsEnumerable()
        .GroupBy(row => row.Field<string>("Name"))
        .Select(g => g.CopyToDataTable()).ToList();

    for (int i = 0; i < dts.Count; i++)
    {
        //Exporting to Excel.
        string folderPath = "D:\\Excel\\";
        if (!Directory.Exists(folderPath))
        {
            Directory.CreateDirectory(folderPath);
        }
        using (XLWorkbook wb = new XLWorkbook())
        {
            if (!string.IsNullOrEmpty(dts[i].Rows[0][0].ToString()))
            {
                wb.Worksheets.Add(dts[i], dts[i].Rows[0][0].ToString());
                wb.SaveAs(folderPath + dts[i].Rows[0][0].ToString() + ".xlsx");
            }
        }
    }
}

private DataTable GetDataTableFromExcel()
{
    DataTable dt = new DataTable();
    string filePath = openFileDialog1.FileName;
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }

    return dt;
}

VB.Net

Private Sub btnGenerateExcel_Click(sender As System.Object, e As EventArgs) Handles btnGenerateExcel.Click
    openFileDialog1.ShowDialog()
End Sub

Private Sub openFileDialog1_FileOk(sender As System.Object, e As CancelEventArgs) Handles openFileDialog1.FileOk
    Dim dt As DataTable = GetDataTableFromExcel()
    Dim dts As List(Of DataTable) = dt.AsEnumerable() _
                                    .GroupBy(Function(row) row.Field(Of String)("Name")) _
                                    .Select(Function(g) g.CopyToDataTable()).ToList()
    For i As Integer = 0 To dts.Count - 1
        ' Exporting to Excel.
        Dim folderPath As String = "D:\Excel\"
        If Not Directory.Exists(folderPath) Then
            Directory.CreateDirectory(folderPath)
        End If
        Using wb As XLWorkbook = New XLWorkbook()
            If Not String.IsNullOrEmpty(dts(i).Rows(0)(0).ToString()) Then
                wb.Worksheets.Add(dts(i), dts(i).Rows(0)(0).ToString())
                wb.SaveAs(folderPath & dts(i).Rows(0)(0).ToString() & ".xlsx")
            End If
        End Using
    Next
End Sub

Private Function GetDataTableFromExcel() As DataTable
    Dim dt As DataTable = New DataTable()
    Dim filePath As String = openFileDialog1.FileName
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
    End Using

    Return dt
End Function

Screenshots

The Excel

The Generated Excels with Names