You can try to record the step as Macro then view the step, and then rewrite them in Python format.
You may refer article below on how to rewrite VBA script in Python, Thanks!!
https://medium.com/towards-data-science/automate-excel-with-python-7c0e8c7c6256
