Why can't I copy and past only ONE specific tab?

[NewWorldRonin]

Hello,
This is my first post, be gentle. :)

I am an expert Excel user who is now venturing into Python in an effort to automate "the boring stuff".

That said, I have what is probably a very basic question, however, I have come to a dead end. Via Python (openpyxl) I would like to cut and paste (values only) one specific tab into a new Excel file. The code I am using below works well in terms of only pasting values and preserving formatting. The downfall is it copies ALL the tabs of the source file.
I am calling out the desired tab name that I am trying to copy ('10_Yr') in line five of said code (shown below), however, it doesn't seem to work. How can I specify only the exact tab(s) that I want to copy? Every search and tutorial that I encounter somehow doesn't address this. Many thanks in advance.

from openpyxl import load_workbook
from copy import copy

wb = load_workbook(r"C:\Original File Name.xlsx", data_only = True)   #your file name
ws = wb['10_Yr'] #your sheet name in the file above

for group in list(ws.merged_cells.ranges):
    min_col, min_row, max_col, max_row = group.bounds
    cell_start = ws.cell(row = min_row, column = min_col)
    top_left_cell_value = cell_start.value

    ws.unmerge_cells(str(group))

    for i_row in range(min_row, max_row + 1):
        for j_col in range(min_col, max_col + 1): 
            ws.cell(row = i_row, column = j_col, value = top_left_cell_value)
            #copy the cell format
            ws.cell(row = i_row, column = j_col).alignment = copy(cell_start.alignment)
            ws.cell(row = i_row, column = j_col).border = copy(cell_start.border)
            ws.cell(row = i_row, column = j_col).font = copy(cell_start.font)

wb.save(r"C:\new file name.xlsx")

[deanhystad]

I don't see where you create a new workbook.

[NewWorldRonin]

I don't see where you create a new workbook.

The last line creates the new workbook. It may be a bit unorthodox but it works on my end.

[deanhystad]

No, the last line writes the existing workbook to a new file. Nowhere do you create a new workbook. You'll want to create a new workbook object, create your new "tab" in the new workbook, then write the new workbook to a file.

[Pedroski55]

You need a source file and a target file. I never dabbled with unmerging cells.

Something like this:

import openpyxl

# the folder with the XL file
path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/Delta_blanko.xlsx'
savepath = '/home/pedro/myPython/openpyxl/xlsx_files/Delta_blanko_changed.xlsx'
sourceFile = openpyxl.load_workbook(path2XL)
sourceSheetNames = sourceFile.sheetnames
for sheet in sourceSheetNames:
    print('This XL has the sheets:', sheet)

sFsheet = input('Copy and paste the name of the sheet you want to copy.')
# create a target file
targetFile = openpyxl.Workbook()
# a new file has 1 sheet called Sheet
targetFileSheetNames = targetFile.sheetnames
ts = targetFile.active
# rename the sheet Sheet to anything you like
ts.title = 'My shiny new sheet'
count = 1
for row in sourceFile[sFsheet].values:
    print(row)
    for i in range(len(row)):
        targetFile['My shiny new sheet'].cell(row=count, column=i+1).value=row[i]
    count+=1

targetFile.save(savepath)

Of course, there are other ways to achieve this, especially if you only want specific columns and/or rows.

[NewWorldRonin]

Thank you for replying...I don't get any errors, but a new file is not produced when running that code. Nothing shows up in the targeted folder (or any folder).

Here is a recap of what I tried:

import openpyxl
 
# the folder with the XL file
path2XL = r'C:\Source_File.xlsx'
savepath = r'C:\New_File_Name.xlsx'
sourceFile = openpyxl.load_workbook(path2XL)
sourceSheetNames = sourceFile.sheetnames
for sheet in sourceSheetNames:
    print(r'C:\Source_File.xlsx', sheet)
 
sFsheet = input('Source File Sheet Name')
# create a target file
targetFile = openpyxl.Workbook()
# a new file has 1 sheet called Sheet
targetFileSheetNames = targetFile.sheetnames
ts = targetFile.active
# rename the sheet Sheet to anything you like
ts.title = 'New Name for Sheet'
count = 1
for row in sourceFile[sFsheet].values:
    print(row)
    for i in range(len(row)):
        targetFile['New Name for Sheet'].cell(row=count, column=i+1).value=row[i]
    count+=1
 
targetFile.save(savepath)

deanhystad write Jan-12-2024, 02:42 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[deanhystad]

The only thing I can think of is that you are not allowed to create files in C:? This code works fine for me. The steps are the same used in your program.

"""Copy sheet from existing spreadsheet to a new file."""

import openpyxl
from pathlib import Path

path = Path("./spreadsheets")  # Where to find spreadsheet files

# Open the existing spreadsheet file and get the worksheet to copy.
source_wb = openpyxl.load_workbook(path / "test.xlsx")
source = source_wb["Sheet1"]

# Make a new workbook and rename the first worksheet.
wb = openpyxl.Workbook()
target = wb[wb.sheetnames[0]]
target.title = "Source File Sheet Name"

# Copy values from source to target.
for row, row_values in enumerate(source.values, start=1):
    for column, value in enumerate(row_values, start=1):
        target.cell(row=row, column=column, value=value)

# Save copy
wb.save(path / "copy.xlsx")

[NewWorldRonin]

Thanks, I actually got it work. There was a problem with my path where the file was saved (my oversight).
The only caveat is only hard-coded values are pasted into the new file, tab, etc.
Is there a way to amend where formulas would be captured and pasted as values?
Many thanks again.

[deanhystad]

I do not understand your question. Please elaborate.

This may answer some of your questions.

https://stackoverflow.com/questions/4234...g-openpyxl