Copy data from one excel sheet to another using openpyxl in Python

In this openpyxl tutorial, we will learn how to copy data from one Excel sheet to another using openpyxl in Python.

We will use the openpyxl library which is basically used for modifying, reading, and writing Excel files, in our Python program.

Program to Copy data from one Excel sheet to another using openpyxl library

Here is the sample Excel sheet with some data in it. Our aim is to copy all the contents of the Sheet1 to Sheet2 of the Excel Workbook.

 

Step1: Import the openpyxl library.

import openpyxl

Step2: Connect/load the Excel file to the program.

path ="//home//sanamsahoo0876//Documents//Book1.xlsx"
workbook = openpyxl.load_workbook(path)

Step3: Initialize variables with the source sheet name and destination sheet name.

sheet1 = workbook["Sheet1"]
sheet2 = workbook["Sheet2"]

Step4: Create two variables to initialize with the total number of rows and columns in the source Excel sheet.

maxr = sheet1.max_row
maxc = sheet1.max_column

Step5: Now, copy all the cell values from the source Excel sheet to the destination Excel sheet.

for r in range (1, maxr + 1):
    for c in range (1, maxc + 1):
        sheet2.cell(row=r,column=c).value = sheet1.cell(row=r,column=c).value

Step6: Lastly, save the changes to the Excel File.

workbook.save("//home//sanamsahoo0876//Documents//Book1.xlsx")

Here is the complete Python Program:

import openpyxl;
  
path ="//home//sanamsahoo0876//Documents//Book1.xlsx"
workbook = openpyxl.load_workbook(path)

sheet1 = workbook["Sheet1"]
sheet2 = workbook["Sheet2"]
    
maxr = sheet1.max_row
maxc = sheet1.max_column
  
workbook.save("//home//sanamsahoo0876//Documents//Book1.xlsx")

Output:

As we can see from the output, the cell values of sheet1 have successfully been copied to sheet2.

Hope you have enjoyed this tutorial and learned how to copy cell values from one sheet to another sheet by using openpyxl in Python.

Happy Coding!!

You can also read, How to get sheet names using openpyxl in Python

4 responses to “Copy data from one excel sheet to another using openpyxl in Python”

1. Aditya says:

   March 1, 2022 at 12:02 am

   Thanks for the code.
   I tried this same in my project and it worked. However, the code hasn’t picked the format. Could you help me with this please.

   Reply
2. joe says:

   September 24, 2022 at 4:34 am

   you just need:
   >>> Sheet2 = wb.copy_worksheet(Sheet1)

   Reply
3. joshuva says:

   November 21, 2022 at 6:54 pm

   Hi i want to create duplicate sheet from existing sheet with in the same book and rename it with date can you ple help me

   Reply
4. shrinand says:

   November 30, 2022 at 11:13 am

   I am getting file corruption error while copying from .xlsx to .xlsm format. Could you please suggest any idea? thanks

   Reply