Split Excel worksheet into multiple worksheets based on column name

split_excel_worksheet.py

from xlwings import Workbook, Range, Sheet
import pandas as pd

# Split Excel data in one worksheet into multiple worksheets based on column name.

# Copy this file into the same location as the Excel workbook with the worksheet you wish to split.
# Download the zip of the xlwings Github repo here: https://github.com/ZoomerAnalytics/xlwings and copy the
# xlwings.bas file from the xlwings folder. Import the xlwings.bas file into your Excel workbook by entering ALT+F11
# and then going to File, Import File and clicking on the file.
# Import the Split_Excel_Worksheet.bas file and run by going to the Developer tab on the Excel Ribbon, click Macros,
# and select Split_Excel_Workbooks

# Make sure the data you want split starts in cell A1 and has no empty column headers.
# The worksheet to split should be the first worksheet in the workbook.

"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Main Script
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

def split_worksheets():

    wb = Workbook.caller()

    sheet = Range('temp', 'AA1').value
    column = Range('temp', 'AA2').value

    data = pd.DataFrame(pd.read_excel(sheet, 0, index_col=None, na_values=[0]))
    data.sort(column, axis = 0, inplace = True)

    split = data.groupby(column)
    for i in split.groups:
        Sheet.add()
        Range('A1', index=False).value = split.get_group(i)

split_excel_worksheet.vba

Attribute VB_Name = "Split_Excel_Workbooks"
Option Explicit

Sub Excel_Worksheet_Split()

Dim wTemp   As String
Dim column  As Variant

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

column = InputBox("Enter column name you want to split on")

wTemp = "temp"

Application.DisplayAlerts = False
On Error Resume Next
Worksheets(wTemp).Delete
Err.Clear

Application.DisplayAlerts = True
Worksheets.Add.Name = wTemp

Sheets("temp").Activate
Range("AA1").Value = ActiveWorkbook.Path
Range("AA2").Value = column

RunPython ("import Split_Excel_Workbook; Split_Excel_Workbook.split_worksheets()")

Application.DisplayAlerts = False
On Error Resume Next
Worksheets(wTemp).Delete
Err.Clear

Application.DisplayAlerts = True

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

End Sub