Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Copy a Sheet Between Excel Workbooks with Python

openpyxl's copy_worksheet only works inside one workbook — here is what actually happens when you copy across files, how to carry values and styles over, and when to copy the file instead.

"Copy this sheet into that workbook" sounds like a one-liner, and openpyxl does have copy_worksheet — but it only copies within a single workbook, and the reason is worth understanding before you go looking for a workaround. A worksheet is not self-contained: its cells reference the workbook's shared string table, its formats index into the workbook's style table, and its formulas may point at defined names that live at workbook level. Move the object and every one of those references points at the wrong place.

So a cross-workbook copy is always a rebuild, and the question becomes which parts you need to carry. This guide, part of Working with Multiple Excel Sheets in Python, covers the three approaches and what each one loses.

Why a worksheet cannot simply move between workbooks A worksheet's cells reference workbook-level tables: shared strings, the style table and defined names. Inside one workbook copy_worksheet can reuse those references. Across workbooks the indices mean different things, so the sheet has to be rebuilt cell by cell in the target. source workbook Sheet "Q1" shared strings style table cell 0 means "North", format 12 means bold currency — in THIS file target workbook new sheet its own strings its own styles index 12 here is a different format, so values and styles must be re-created rebuild

Prerequisites

Bash
pip install openpyxl

Two workbooks to work with. The examples generate them, so the whole guide runs as written.

Step 1: Create a source and a target

Python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

src = Workbook()
ws = src.active
ws.title = "Q1"
ws.append(["Region", "Amount"])
ws.append(["North", 150.25])
ws.append(["South", 274.75])
ws.append(["Total", "=SUM(B2:B3)"])

for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", start_color="1F4E78")
for cell in ws["B"][1:]:
    cell.number_format = '#,##0.00'
ws.column_dimensions["A"].width = 16
ws.column_dimensions["B"].width = 14
ws.freeze_panes = "A2"
src.save("source.xlsx")

target = Workbook()
target.active.title = "Overview"
target.save("target.xlsx")

Step 2: Copying within one workbook

Inside a single file, openpyxl does it properly — including styles, dimensions and merged ranges:

Python
from openpyxl import load_workbook

wb = load_workbook("source.xlsx")
copy = wb.copy_worksheet(wb["Q1"])
copy.title = "Q1 (working)"
wb.save("source.xlsx")

This is the case copy_worksheet is for, and it is worth knowing it exists because the common use — duplicating a template tab per region before filling it in — lives entirely inside one workbook. Note that it copies values and formatting but not images or charts, which openpyxl does not duplicate.

Step 3: Copying across workbooks, cell by cell

Across files, write the copy yourself. The version below carries values, number formats, fonts, fills, borders, alignment, column widths, row heights and merged ranges:

Python
from copy import copy as copy_style

from openpyxl import load_workbook


def copy_sheet(source_ws, target_wb, title=None, values_only=False):
    """Re-create a worksheet in another workbook. Returns the new sheet."""
    new = target_wb.create_sheet(title or source_ws.title)

    for row in source_ws.iter_rows():
        for cell in row:
            fresh = new.cell(row=cell.row, column=cell.column, value=cell.value)
            if values_only or not cell.has_style:
                continue
            fresh.font = copy_style(cell.font)
            fresh.fill = copy_style(cell.fill)
            fresh.border = copy_style(cell.border)
            fresh.alignment = copy_style(cell.alignment)
            fresh.number_format = cell.number_format
            fresh.protection = copy_style(cell.protection)

    for letter, dim in source_ws.column_dimensions.items():
        new.column_dimensions[letter].width = dim.width
        new.column_dimensions[letter].hidden = dim.hidden
    for idx, dim in source_ws.row_dimensions.items():
        new.row_dimensions[idx].height = dim.height

    for rng in source_ws.merged_cells.ranges:
        new.merge_cells(str(rng))

    new.freeze_panes = source_ws.freeze_panes
    new.sheet_format.defaultColWidth = source_ws.sheet_format.defaultColWidth
    return new


src_wb = load_workbook("source.xlsx")
tgt_wb = load_workbook("target.xlsx")
copy_sheet(src_wb["Q1"], tgt_wb, title="Q1 copy")
tgt_wb.save("target.xlsx")

copy(cell.font) rather than fresh.font = cell.font is the detail that matters. openpyxl style objects are shared and immutable-by-convention; assigning the source's object directly binds a style belonging to another workbook's table, and the result is either ignored on save or carried incorrectly. Copying the object first gives the target workbook its own instance to register.

cell.has_style skips the work for unstyled cells, which on a large sheet is most of them and makes the copy noticeably faster.

Step 4: Decide what to do about formulas

A copied formula is copied as text — =SUM(B2:B3) still says =SUM(B2:B3) in the new file. That is correct when the supporting rows came too, and wrong when they did not. When the target only needs numbers, read the source with data_only=True:

Python
values_wb = load_workbook("source.xlsx", data_only=True)
copy_sheet(values_wb["Q1"], tgt_wb, title="Q1 values")
tgt_wb.save("target.xlsx")

data_only=True returns the value Excel cached the last time it saved the file. If the workbook was generated by openpyxl and never opened in Excel, there is no cached value and every formula cell reads as None — the behaviour explained in Read Formula Results with openpyxl data_only. Check for that before relying on it, or compute the totals in Python instead.

What survives a cell-by-cell copy and what does not Values, number formats, fonts, fills, borders, alignment, column widths, row heights, merged ranges and frozen panes all carry across when copied explicitly. Charts, images, conditional formatting, data validation, comments, defined names and pivot tables do not, and have to be recreated on the target sheet. carried by the loop above cell values and formula text number formats font, fill, border, alignment column widths and row heights merged ranges and frozen panes everything a reader sees in the grid not carried — recreate or copy the file charts and images conditional formatting rules data validation dropdowns comments and defined names pivot tables and slicers objects that live above the cell grid

Step 4b: Decide which route the job actually needs

Three approaches, and the choice is usually settled by one question — does the target workbook already have content that must be kept?

Choosing between duplicating, rebuilding and copying the file If the source and the target are the same workbook, copy_worksheet does everything. If the target is a new file, copying the whole file and deleting the unwanted sheets keeps charts and rules intact. Only when the sheet must join an existing workbook with content of its own is the cell-by-cell rebuild necessary. same workbook wb.copy_worksheet(ws) values, styles, widths, merges — all handled one line, nothing lost a new file shutil.copy, then delete charts, images, rules and validation survive nothing is rebuilt into an existing book rebuild cell by cell recreate charts, rules and validation yourself the only route that works

Most requests that sound like the third case are really the second. "Put the Q1 sheet into a workbook for the auditor" does not need an existing target — it needs a file containing that sheet, which the copy-and-delete route produces in two lines with nothing lost. Reach for the rebuild only when the destination workbook is genuinely established and must keep what it already holds.

Step 5: When the answer is to copy the file

If the sheet carries charts, validation or conditional formatting, rebuilding it faithfully is more work than it is worth. Copy the whole file and remove what you do not want:

Python
import shutil

from openpyxl import load_workbook

shutil.copy("source.xlsx", "extract.xlsx")

wb = load_workbook("extract.xlsx")
for name in list(wb.sheetnames):
    if name != "Q1":
        del wb[name]
wb.save("extract.xlsx")

Nothing is rebuilt, so nothing is lost — charts, images, rules and all. The limitation is that this produces a new file containing the sheet, rather than adding the sheet to an existing workbook that already has content of its own. When the requirement really is "add this sheet to that established workbook", the cell-by-cell copy plus recreating the extras is the only route openpyxl offers.

Copying rules and validation onto the new sheet is straightforward if you know what they were, and both are covered in Applying Conditional Formatting with openpyxl and Add Dropdown Data Validation to Excel with openpyxl.

Common pitfalls and gotchas

SymptomCauseFix
ValueError from copy_worksheetUsed across two workbooksCopy cell by cell, or copy the file
Styles missing after the copyStyle objects assigned, not copiedcopy(cell.font) from the copy module
Formulas show #REF!Referenced rows were not copiedCopy the supporting range, or copy values
Formula cells read as Nonedata_only=True on a file Excel never openedCompute in Python, or open and save once in Excel
Charts and images vanishedNot handled by a cell copyCopy the file, or recreate them
Columns are all default widthColumn dimensions not copiedCopy column_dimensions explicitly
Merged cells lostMerged ranges not copiedIterate merged_cells.ranges
Copy is slow on a large sheetStyling every cell including emptiesSkip cells where has_style is false

Performance and scale notes

A cell-by-cell copy touches every cell twice — once to read, once to write — and building a style object per cell is the expensive half. On a 100,000-row sheet, skipping unstyled cells and setting formats at the column level instead can cut the copy time substantially.

For bulk work, shutil.copy is effectively free regardless of size, because it never parses the workbook at all. That makes the file-copy route the right default whenever the requirement can be expressed as "this workbook, minus some sheets" rather than "this sheet, added to that workbook".

Conclusion

copy_worksheet duplicates a sheet inside one workbook and nothing more, because a worksheet's cells point at workbook-level string and style tables. Across files, rebuild: copy values and formula text, copy each style object rather than assigning it, and carry the column widths, row heights, merged ranges and panes. Decide deliberately whether formulas should travel as formulas or as values. And when the sheet carries charts, rules or validation, copy the file and delete the other sheets — nothing rebuilt is nothing lost.

Frequently asked questions

Why does copy_worksheet fail across workbooks? It is documented as working only within the same workbook. A worksheet holds references to its parent's shared strings, styles and defined names, so moving the object itself would carry broken references — openpyxl raises rather than producing a corrupt file.

What is the simplest way to copy a sheet to another file? Copy the whole file with shutil.copy and delete the sheets you do not want. Everything survives, because nothing was rebuilt.

Do charts and images copy across? Not with a cell-by-cell copy. openpyxl does not re-anchor chart or image objects into a different workbook, so those have to be recreated on the target.

How do I copy only the values? Read the source with data_only=True and write the values into the target sheet. That gives numbers instead of formulas, which is what you want when the target has no supporting data.

Up to the parent guide:

Related guides: