How to use Worksheet.Copy in the xlwings API way

The Copy method of the Worksheet object in xlwings is a powerful tool for duplicating worksheets within or across workbooks. This functionality is essential for tasks such as creating templates, backing up data, or reorganizing workbook structures without manual copying and pasting. By leveraging the Excel object model through xlwings, users can automate these processes efficiently in Python.

In xlwings, the Copy method is accessed via the api property, which provides direct access to the underlying Excel object model. The syntax follows the pattern of the Excel VBA Copy method, where you specify the location for the copied sheet. The method signature is Copy(Before, After), with both parameters being optional. The Before parameter accepts a Worksheet object indicating the sheet before which the copy should be placed, while After specifies the sheet after which the copy should be inserted. If neither Before nor After is provided, Excel creates a new workbook to hold the copied worksheet. It’s important to note that you cannot use both Before and After simultaneously; specifying one excludes the other. These parameters allow precise control over the placement of the duplicated sheet within the workbook’s tab order.

For example, to copy a worksheet named “DataSheet” and place it before an existing sheet called “SummarySheet” in the same workbook, you can use the following xlwings code:

import xlwings as xw
wb = xw.Book("example.xlsx")
data_sheet = wb.sheets["DataSheet"]
summary_sheet = wb.sheets["SummarySheet"]
data_sheet.api.Copy(Before=summary_sheet.api)

This code snippet opens a workbook, references the “DataSheet” and “SummarySheet”, and copies “DataSheet” to appear directly before “SummarySheet”. The copied sheet will automatically be named “DataSheet (2)” by Excel to avoid naming conflicts.

Another common use case is copying a worksheet to a new workbook. By omitting both Before and After parameters, Excel generates a new workbook containing only the copied worksheet. For instance:

import xlwings as xw
wb = xw.Book("source.xlsx")
source_sheet = wb.sheets["SourceSheet"]
source_sheet.api.Copy()

After executing this, a new Excel workbook will open with a worksheet named “SourceSheet” that is a duplicate of the original. This is particularly useful for exporting specific sheets to separate files for distribution or analysis.

When copying between different workbooks, you need to reference the target workbook’s worksheets for the Before or After parameters. For example, to copy “Sheet1” from one workbook and place it after “SheetA” in another workbook:

import xlwings as xw
wb1 = xw.Book("workbook1.xlsx")
wb2 = xw.Book("workbook2.xlsx")
sheet1 = wb1.sheets["Sheet1"]
sheeta = wb2.sheets["SheetA"]
sheet1.api.Copy(After=sheeta.api)