Python Openpyxl change cell A1 in all excel worksheets within a workbook to equal the name of the worksheet tab

Question

I'm new to Python, therefore I'm sure my query to you guys is quite simple. I'm attempting to use Python and OpenPyXL to complete the following tasks in the code below. The third step causes the code to fail after the first two phases, which is acceptable. Please let me know why my for loop isn't working. Even if it appears to be quite wrong right now, I am certain that I am moving further away from the right course after trying so many different combinations.

This code is attempting to:

1. Create a new excel workbook

2. Create new worksheets within the workbook based on a list of sheet names.

3. Set cell A1 of each worksheet equal to the worksheet name. IE: if the worksheet tab is called "Total Pivot", then I want cell A1 in that sheet to contain the text "Total Pivot". The code worked well until I added this portion. It is currently giving me an error that says-- 'WriteOnlyWorksheet' object is not subscriptable.

Thanks so much for any help. I will be very grateful for any advice. :)

from openpyxl import Workbook

#Create new workbook
Report1_workbook = Workbook('Report 1.xlsx')

#List of worksheets to create in Report 1
report1sheets = ["Total Summary", "Interest Summary", "Price Summary", "Total Pivot", "Total Pivot Expanded"]

#Creating worksheets with tab names in the above list.
namereport1sheets = [Report1_workbook.create_sheet(title=x) for x in report1sheets]

#Setting cell A1 in every sheet to be equal to the sheet title-- THIS IS WHAT IS CAUSING AN ERROR

for sheet in Report1_workbook.sheetnames:
    for i in range(len(report1sheets)):
        sheet["A1"].value = report1sheets[i]

Report1_workbook.save('Report 1.xlsx')

Answer 1

You made a mistake when you used the statement Report1 workbook = Workbook('Report 1.xlsx'). Cell. value will not function because the file will be opened in Write-only mode. Additionally, it is cleaner to use the worksheet and title rather than the several loops. The quickest/shortest fix is to use add (). Below is the updated code.

from openpyxl import Workbook
#Create new workbook
Report1_workbook = Workbook('Report 1.xlsx')

#List of worksheets to create in Report 1
report1sheets = ["Total Summary", "Interest Summary", "Price Summary", "Total Pivot", "Total Pivot Expanded"]

#Creating worksheets with tab names in the above list.
namereport1sheets = [Report1_workbook.create_sheet(title=x) for x in report1sheets]

for sheet in Report1_workbook.worksheets: # For each worksheet.. 
    sheet.append([sheet.title])  ## Use append and use sheet.title=name of sheet

Report1_workbook.save('Report 1.xlsx')

I hope this helps you.