xl.py

import string
import openpyxl
from openpyxl.workbook.workbook import Workbook

# load the source file
filename = input("Enter a file name:")
wb = openpyxl.load_workbook(filename='filename', data_only=True)

# method to initialize a target wb:
def create_wb(id, name, o_sheet):
    print("Creating a new file for ", name)
    create_wb.twb = Workbook()
    create_wb.dest_filename = str(id) + "_" + name + ".xlsx"
    create_wb.ws = create_wb.twb.active
    create_wb.ws.title = "Data"
    alphabet_list = list(string.ascii_uppercase)
    for i in range(o_sheet.max_column):
        create_wb.ws[alphabet_list[i]+str(1)].value = o_sheet[alphabet_list[i]+str(1)].value

# boilerplate data
alphabet_list = list(string.ascii_uppercase)

# fetch worksheet to work with
a_sheet_names = wb.sheetnames
print("Available sheet names:", a_sheet_names)
sheet_name = input("Enter a sheet name: ")
o_sheet = wb[sheet_name]

# fetch initial id to work with
id = o_sheet['A2'].value
name = o_sheet['B2'].value

# create initial file
create_wb(id, name, o_sheet)

# counters
counter_row = 2

# loop through rows
for i in range(2, o_sheet.max_row):
    if o_sheet.cell(row=i, column=1).value == None:
        create_wb.twb.save(filename = create_wb.dest_filename)
        break

    elif o_sheet.cell(row=i, column=1).value == id:
        # append data
        for j in range(o_sheet.max_column):
            create_wb.ws[alphabet_list[j]+str(counter_row)].value = o_sheet[alphabet_list[j]+str(i)].value
        
        # increment counter
        counter_row = counter_row + 1

    elif o_sheet.cell(row=i, column=1).value != id:
        # save existing file
        create_wb.twb.save(filename = create_wb.dest_filename)

        # initialize new file
        id = o_sheet.cell(row=i, column=1).value
        name = o_sheet.cell(row=i, column=2).value
        create_wb(id, name, o_sheet)

        # reset counter
        counter_row = 2

        # add current data
        for j in range(o_sheet.max_column):
            create_wb.ws[alphabet_list[j]+str(counter_row)].value = o_sheet[alphabet_list[j]+str(i)].value
        
        # increment counter
        counter_row = counter_row + 1