I have a function that exports dataframes to excel:

def export_grouped_df(list, path):

    rowPos = 1
    if(os.path.exists(path)):
        with pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
            for item in list:
                item.loc['Total', 'MTMValue'] = item['MTMValue'].sum()
                item.to_excel(writer, sheet_name='Counterparty Marks', startrow=rowPos, float_format = "%0.5f", index=False)
                rowPos += (len(item) + 2)
    else:
        with pd.ExcelWriter(path, engine='openpyxl', mode='w') as writer:   
            for item in list:
                item.loc['Total', 'MTMValue'] = item['MTMValue'].sum()
                item.to_excel(writer, sheet_name='Counterparty Marks', startrow=rowPos, float_format = "%0.5f", index=False)
                rowPos += (len(item) + 2)

If I were to export a different dataframe to the same sheet, the data would overlay the original data which is expected, but not the functionality I want.

If I change the if-sheets-exist = 'replace', I expect it to overwrite the data on the sheet but it doesn’t. It seems that if-sheets-exist = 'replace' and if-sheets-exist = 'new'
have the same functionality.

Is there a way to overwrite an existing sheet, while preserving the rest of the workbook and exporting multiple dataframes?