Python script to loop through column to select specific cell ranges and paste special transpose on next sheet

Question

I am new to scripting and get the basic ideas of looping and the such. I want to loop through a column of a worksheet selection specific cell ranges and paste special transpose on the next sheet.

I found a script to select a range and do the transposing part, I'm just having difficulty figuring out the looping and adding to the cell start.

In my head I want to loop through column B starting at 2(bx) to 19(by) to copy. The next set of cells I would add 6 to x to start the copy for 17 rows

This example may be way off but:

Bx = 2
By = 19
For B:B 
 copy range Bx:By 
 def copy_transpose_paste_values(workbook_path, source_sheet_name, source_range, destination_sheet_name, destination_start_cell):
 """
 Copies a cell range, transposes it, and pastes it as values into another sheet.
 Args:
 workbook_path (str): Path to the Excel workbook.
 source_sheet_name (str): Name of the sheet to copy from.
 source_range (str): Cell range to copy (e.g., "A1:B5").
 destination_sheet_name (str): Name of the sheet to paste to.
 destination_start_cell (str): Top-left cell to paste to (e.g., "D1").
 """
 workbook = load_workbook(workbook_path)
 source_sheet = workbook[source_sheet_name]
 destination_sheet = workbook[destination_sheet_name]
 # Get the values from the source range
 data = []
 for row in source_sheet[source_range]:
 values_row = []
 for cell in row:
 values_row.append(cell.value)
 data.append(values_row)
 # Transpose the data
 transposed_data = list(zip(*data))
 # Paste the transposed data as values
 start_col = destination_start_cell[0]
 start_row = int(destination_start_cell[1:])
 for row_idx, row_data in enumerate(transposed_data):
 for col_idx, cell_value in enumerate(row_data):
 cell = destination_sheet.cell(row=start_row + row_idx, column=ord(start_col) - ord('A') + 1 + col_idx)
 cell.value = cell_value
 workbook.save(workbook_path)
if __name__ == '__main__':
 # Replace with your actual file path, sheet names, and ranges
 workbook_path = 'C:/test/cpt.xlsx'
 source_sheet_name = 'Sheet1'
 source_range = 'Bx:By'
 destination_sheet_name = 'Sheet2'
 destination_start_cell = 'D1'
 copy_transpose_paste_values(workbook_path, source_sheet_name, source_range, destination_sheet_name, destination_start_cell)
Bx+6 = Bx By+6=By

I appreciate any and all help on solving this.

Thanks

Images of Sheet 1 and 2

enter image description here

enter image description here

• furas: if you want to loop then maybe use while-loop and update parameters inside this loop, Bx = Bx+6 By = By+6, and run function inside this loop - so it should use new values.
• jackal: It would help if you showed an example of a minimal input sheet and what the output sheet should look like
• Lee: I don't know how to insert a spread sheet.
• P.Harrison: Here is how you can add a screen shot: meta.stackoverflow.com/questions/344851/…
• Lee: Thank you. I added images also realized my counts were off second set would have had to been BX= BY+6 BY= BY+24