How to use Worksheets.FillAcrossSheets in the xlwings API way

The FillAcrossSheets member of the Worksheets object in Excel is a powerful method used to copy a specified range from one worksheet to the same range on all other worksheets within the same workbook. This functionality is particularly useful when you need to maintain consistent formatting, formulas, or static data (like headers or standard values) across multiple sheets in a report or dashboard. Instead of manually copying and pasting to each sheet, FillAcrossSheets automates this process, ensuring uniformity and saving significant time.

In the Excel object model, accessed via xlwings, the syntax for this method is as follows:

Worksheets.FillAcrossSheets(Range, Type)

The method requires two parameters:

• Range: This is a required parameter. It specifies the range of cells to be copied across the worksheets. In xlwings, this is typically provided as an xlwings.Range object. You can define it using the Range property on a sheet object, for example, sheet.range("A1:D10").
• Type: This is an optional parameter that determines what content from the source range is copied. It accepts values from the XlFillWith enumeration. The most commonly used values are:
• xlFillWithAll (default, value = -4104): Copies everything from the source range—content, formulas, and formatting.
• xlFillWithContents (value = 2): Copies only values and formulas, but not the cell formatting.
• xlFillWithFormats (value = -4122): Copies only the cell formatting (like font, color, borders), but not the values or formulas.

If the Type parameter is omitted, the default behavior is xlFillWithAll.

Example Usage with xlwings:

Imagine you have a workbook with three sheets named “Q1”, “Q2”, and “Q3”. You want to set up a standard header in cells A1 through D1 on every sheet. You would write the header on the “Q1” sheet and then use FillAcrossSheets to propagate it.

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active

# Specify the workbook (use .books.active for the active workbook)
wb = app.books['Financials.xlsx']

# Define the range to copy (the header on the first sheet)
source_range = wb.sheets['Q1'].range('A1:D1')
source_range.value = ['Product', 'Region', 'Sales', 'Target'] # Set the header values
source_range.font.bold = True # Apply some formatting

# Use FillAcrossSheets to copy this range to all other worksheets in the workbook
# This targets the Worksheets collection of the specific workbook.
wb.sheets.api.FillAcrossSheets(source_range.api)

# To copy only the formatting of a complex template area:
template_range = wb.sheets['Template'].range('A1:F20')
wb.sheets.api.FillAcrossSheets(template_range.api, Type=-4122) # xlFillWithFormats

# Save and close
wb.save()
wb.close()
app.quit()