{"id":2286,"date":"2026-09-03T16:07:03","date_gmt":"2026-09-03T08:07:03","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2286"},"modified":"2026-03-28T12:31:05","modified_gmt":"2026-03-28T12:31:05","slug":"how-to-use-worksheetpastespecial-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetpastespecial-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.PasteSpecial in the xlwings API way"},"content":{"rendered":"\n

The PasteSpecial<\/code> method of the Worksheet<\/code> object in Excel is a powerful feature for pasting data with specific attributes, such as values, formats, or formulas, rather than a simple copy-paste. In xlwings, this functionality is accessible through the api<\/code> property, which provides direct access to the underlying Excel object model. This allows for precise control over how data is transferred between ranges or applications, making it essential for tasks like consolidating reports, applying number formats, or skipping blanks during data integration.<\/p>\n\n\n\n

Functionality<\/strong>
The primary purpose of PasteSpecial<\/code> is to paste clipboard contents into a worksheet range with specified options. Unlike the standard Paste<\/code> method, it enables selective pasting\u2014for example, pasting only the values from copied cells while discarding formulas, or pasting only the column widths. This is particularly useful when you need to manipulate data without altering underlying formulas or when preparing data for presentation.<\/p>\n\n\n\n

Syntax<\/strong>
In xlwings, you call PasteSpecial<\/code> via the Excel object model. The method is applied to a Range<\/code> object where the paste will occur. The basic syntax is:<\/p>\n\n\n\n

sheet.range(\"A1\").api.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)<\/code><\/pre>\n\n\n\n
The parameters are:<\/p>\n\n\n\n
    \n
  • Paste<\/code>: Specifies the part of the copied data to paste. It is an enumeration from the XlPasteType<\/code> constants. Common values include:<\/li>\n\n\n\n
  • xlPasteAll<\/code> (-4104): Pastes everything.<\/li>\n\n\n\n
  • xlPasteValues<\/code> (-4163): Pastes only values.<\/li>\n\n\n\n
  • xlPasteFormats<\/code> (-4122): Pastes only formats.<\/li>\n\n\n\n
  • xlPasteFormulas<\/code> (-4123): Pastes only formulas.<\/li>\n\n\n\n
  • Operation<\/code>: Optional. Specifies a mathematical operation to apply during the paste, from the XlPasteSpecialOperation<\/code> constants. For example, xlPasteSpecialOperationAdd<\/code> (2) adds the copied data to the destination values. If omitted, no operation is performed.<\/li>\n\n\n\n
  • SkipBlanks<\/code>: Optional. A boolean (True<\/code> or False<\/code>) that, when set to True<\/code>, prevents blank cells from the copied range from overwriting existing data in the destination.<\/li>\n\n\n\n
  • Transpose<\/code>: Optional. A boolean that, when True<\/code>, transposes rows and columns during the paste.<\/li>\n<\/ul>\n\n\n\n
    These parameters are passed as keyword arguments in Python, and you can refer to Excel’s VBA documentation for exact constant values. In practice, you often use numeric equivalents (e.g., -4163 for xlPasteValues<\/code>).<\/p>\n\n\n\n
    Code Examples<\/strong>
    Here are practical xlwings API examples demonstrating PasteSpecial<\/code>:<\/p>\n\n\n\n
      \n
    1. Paste Values Only<\/strong>: Copy data from one range and paste only the values into another, ignoring formulas and formats.<\/li>\n<\/ol>\n\n\n\n
      import xlwings as xw\nwb = xw.Book(\"example.xlsx\")\nsheet = wb.sheets[\"Sheet1\"]\n# Copy data from A1:B5\nsheet.range(\"A1:B5\").api.Copy()\n# Paste only values into D1\nsheet.range(\"D1\").api.PasteSpecial(Paste=-4163) # xlPasteValues\n# Clear clipboard to avoid persistent paste prompts\nwb.app.api.CutCopyMode = False<\/code><\/pre>\n\n\n\n
        \n
      1. Paste Formats and Transpose<\/strong>: Copy a range and paste only its formatting to a new location, while also transposing the layout.<\/li>\n<\/ol>\n\n\n\n
        import xlwings as xw\nwb = xw.Book(\"report.xlsx\")\nsheet = wb.sheets[\"Data\"]\n# Copy the header range\nsheet.range(\"A1:D1\").api.Copy()\n# Paste formats with transposition to A10\nsheet.range(\"A10\").api.PasteSpecial(Paste=-4122, Transpose=True) # xlPasteFormats\nwb.app.api.CutCopyMode = False<\/code><\/pre>\n\n\n\n
          \n
        1. Paste with Operation and Skip Blanks<\/strong>: Copy a range of numbers and add them to an existing dataset, skipping any blanks in the copied data.<\/li>\n<\/ol>\n\n\n\n
          import xlwings as xw\nwb = xw.Book(\"budget.xlsx\")\nsheet = wb.sheets[\"Summary\"]\n# Copy values from a source range\nsheet.range(\"F1:F10\").api.Copy()\n# Paste with addition operation, skipping blanks\nsheet.range(\"G1\").api.PasteSpecial(Paste=-4163, Operation=2, SkipBlanks=True)\nwb.app.api.CutCopyMode = False<\/code><\/pre>\n\n\n\n
          <\/p>\n","protected":false},"excerpt":{"rendered":"
          The `PasteSpecial` method of the `Worksheet` object in Excel is a powerful feature for pasting data …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2286","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2286"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2286\/revisions"}],"predecessor-version":[{"id":3488,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2286\/revisions\/3488"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}