Python openpyxl: Create, Delete, Copy Multiple Sheets

When you work with Excel files in Python, openpyxl is a popular library for reading and writing .xlsx workbooks. Managing multiple sheets—creating, deleting, and copying them—is a common task. This article explains the Workbook API for those operations, shows working examples, and notes important limitations.

Creating Sheets in a Workbook

The Workbook.create_sheet() method adds a new sheet. By default, it appends the sheet at the end, but you can control the position with the index parameter.

from openpyxl import Workbook

wb = Workbook()
# The workbook always starts with one sheet named 'Sheet'
default_sheet = wb.active

# Create a new sheet at the end
ws1 = wb.create_sheet(title='Data')

# Create a sheet at the beginning (index 0)
ws2 = wb.create_sheet(title='Summary', index=0)

# Rename the default sheet
default_sheet.title = 'Raw Data'

The title parameter is optional; if omitted, openpyxl generates names such as Sheet1, Sheet2, and so on. The index parameter is zero-based, so index=0 places the sheet at the front. To move a sheet after creation, use wb.move_sheet(sheet, offset).

Deleting Sheets Safely

Use wb.remove(sheet) to delete by worksheet object, or del wb['Sheet Name'] to delete by name.

# Remove a sheet by object
wb.remove(ws1)

# Remove by name using del
del wb['Data']

openpyxl requires at least one sheet in a workbook. If you attempt to remove the last sheet, it raises a ValueError. Create a replacement before deleting the last sheet. If you delete the active sheet, openpyxl makes the first remaining sheet active.

Copying Sheets Within a Workbook

Workbook.copy_worksheet() duplicates a sheet within the same workbook. The copy includes cell values and common formatting such as number formats, fonts, fills, borders, alignment, column widths, and row heights. The copy is added at the end by default.

wb = Workbook()
ws = wb.active
ws.title = 'Original'
ws['A1'] = 'Data'

# Copy the sheet
ws_copy = wb.copy_worksheet(ws)
ws_copy.title = 'Copy'

The copied worksheet is independent; changes to the copy do not affect the original. However, copy_worksheet is not a complete clone. Embedded objects such as images and charts may need to be re-added, and some settings such as page setup or conditional formatting may not survive. Formulas are copied unchanged; because the formulas land in the same cell coordinates, they refer to the same relative cells in the copied sheet.

Copying Sheets Between Workbooks

copy_worksheet only works within one workbook. To transfer data between separate workbooks, copy cell values and formatting manually.

from copy import copy
from openpyxl import Workbook, load_workbook

src_wb = load_workbook('source.xlsx')
src_ws = src_wb['Data']

dst_wb = Workbook()
dst_ws = dst_wb.active
dst_ws.title = 'Data'

for row in src_ws.iter_rows():
    for cell in row:
        dst_cell = dst_ws[cell.coordinate]
        dst_cell.value = cell.value
        if cell.has_style:
            dst_cell.font = copy(cell.font)
            dst_cell.border = copy(cell.border)
            dst_cell.fill = copy(cell.fill)
            dst_cell.number_format = cell.number_format
            dst_cell.protection = copy(cell.protection)
            dst_cell.alignment = copy(cell.alignment)

This copies values and common style attributes. It does not copy merged cells, column widths, or row heights; add those separately if needed. If you need a complete copy of a whole workbook, you can load the file and save it under a new name, but that copies every sheet in the file, not an individual sheet.

Preserving Formatting and Data When Copying

Within one workbook, copy_worksheet copies common cell styles, column widths, and row heights. It may not copy page setup, print settings, or conditional formatting; reapply those on the duplicate if they matter.

For manual copies between workbooks, the example above copies common style attributes but not merged cells. You can also copy merged ranges:

for merged_range in src_ws.merged_cells.ranges:
    dst_ws.merge_cells(str(merged_range))

Performance and Memory Considerations

openpyxl loads an entire workbook into memory when you use load_workbook. Large files with many sheets can therefore consume significant RAM. Copying a worksheet keeps the original and the copy in memory, increasing memory use.

For large transfers between workbooks, you can read the source workbook with read_only=True and write to the destination with write_only=True, copying only cell values. This avoids holding two full workbooks in memory, but it also loses formatting, so use it only when you need raw data.

Common Pitfalls and How to Avoid Them

A frequent error is renaming a copied sheet to a name that already exists. openpyxl generates a unique title for the copy, but if you manually assign a title that is already used, openpyxl can raise a ValueError. Always check wb.sheetnames before assigning a new title.

Deleting sheets while iterating over the workbook's sheet list can cause skipped sheets. Collect the sheets to delete first, then remove them in a separate loop.

Finally, copy_worksheet does not make the copied sheet active. If you want the copy to be the active sheet, set wb.active = wb.sheetnames.index(ws_copy.title) after copying.