How to use Worksheets.Copy in the xlwings API way

The Copy method of the Worksheets object in Excel VBA is mirrored in xlwings through the api property, which provides direct access to the underlying Excel object model. This method is used to duplicate one or more worksheets, placing the copies either before or after a specified sheet in the workbook, or into a new workbook entirely. It is particularly useful for creating templates, generating reports, or backing up data without altering the original sheets.

Syntax in xlwings:

The method is accessed via the api property of a sheet object. The general syntax is:

sheet.api.Copy(Before, After)

• sheet: This is the xlwings Sheet object representing the worksheet you want to copy. You typically obtain it via wb.sheets['SheetName'] or wb.sheets[0].
• Before (Optional, Variant): A sheet object before which the copied sheet(s) will be placed. You cannot specify both Before and After.
• After (Optional, Variant): A sheet object after which the copied sheet(s) will be placed. You cannot specify both Before and After.

Parameter Behavior:

• If neither Before nor After is specified, Excel creates a new workbook and places the copied sheet(s) there. The new workbook becomes the active workbook.
• You must provide either the Before or After argument to place the copy within the same workbook. These arguments are passed as Excel sheet objects, which you can get via the .api property of an xlwings Sheet object.

Examples:

1. Copy a sheet to a new workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Data']
source_sheet.api.Copy() # Creates a new workbook with the copied sheet

2. Copy a sheet and place it before a specific sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Source']
target_sheet = wb.sheets['Target'] # The sheet before which we place the copy
source_sheet.api.Copy(Before=target_sheet.api)
# The new copy will be named "Source (2)" and appear before "Target".

3. Copy a sheet and place it after the last sheet in the same workbook:

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
source_sheet = wb.sheets['Report']
all_sheets = wb.sheets
last_sheet = all_sheets[len(all_sheets) - 1] # Get the last sheet object
source_sheet.api.Copy(After=last_sheet.api)
# The copy is placed at the end of the workbook.

4. Copy multiple worksheets (the entire Worksheets collection):
   To copy multiple sheets, you use the Worksheets collection’s Copy method. In xlwings, you can access this via the workbook’s sheets collection api.

import xlwings as xw
wb = xw.Book('MyWorkbook.xlsx')
# Select specific sheets to copy (e.g., first and third sheet)
wb.api.Worksheets([1, 3]).Copy() # Creates a new workbook with copies of sheets 1 and 3.
# Note: The index [1, 3] uses VBA's 1-based indexing.