Error while copy pasting ranges across sheets xlwings

Question

I want to copy paste ranges for sheets in my .xlsm workbook using xlwings.I want to maintain the source formatting and the leading single quote because of which I've used the .copy , .paste method. I'm getting this error while trying to run the code (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Select method of Range class failed', 'xlmain11.chm', 0, -2146827284), None) after sheet 1 is successfully copied . The first sheet in the loop is copied properly but the above mentioned error is displayed after completion of sheet 1. This is the code I used to copy paste the ranges across sheets :

wb = xw.Book('standalone.xlsm')
sheet_names = ['Sheet1','Sheet2']
for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').api.SpecialCells(12).copy
    sht.range('B1').select()
    sht.api.paste
    sht.api.Application.CutCopyMode=0
wb.save('standalone.xlsm')
wb.close()


PS :- I don't want to use `.value` method of copying ranges as it doesn't preserve source formatting and cells which start with single quote 

Answer 1

Mouwsy's answer works fine. Alternatively you can use pastespecial and with it you wont have to select the sheet

import xlwings as xw

wb = xw.Book('test.xlsx')
sheet_names = ['Sheet1', 'Sheet2']
for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').api.SpecialCells(12).copy
    sht.range("R1").api.PasteSpecial("-4104")
    sht.api.Application.CutCopyMode=0
wb.save('standalone.xlsm')
wb.close()

Update: Just found out xlwings already has a copy and paste method so a simple copy paste will be:

for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').copy()
    sht.range("R1").paste()
    sht.api.Application.CutCopyMode=0

This preserves source formatting. I cant find any other options for customising a copy and paste operation from the docs so I guess you'll still need the api for complete control.