Excel tables and move rows

[Larz60+]

you can calculate the last line of second spreadsheet (assume worksheet name ws2) using
last_row = ws2.max_row
then obviously next row is last_row + 1
so that will give you copy destination.

This will append row 1 sheet 1 to sheet2
example:

from openpyxl import Workbook, load_workbook, cell
import os

os.chdir(os.path.abspath(os.path.dirname(__file__)))

def append_row():
    # append sheet 1 row 1 to wb2 at end
    wb1 = load_workbook('Sheettest.xlsx')
    wb2 = load_workbook('Sheet1test.xlsx')    
    ws1 = wb1.worksheets[1]
    ws2 = wb2.worksheets[1]
    ws2_lastrow = ws2.max_row
    ws1_num_columns = ws1.max_column
    print(ws2.max_row, ws1_num_columns)

    for cellno in range(1, ws1_num_columns):
        ws2.cell(row=ws2_lastrow+1, column = cellno).value = ws1.cell(row=1, column=cellno).value

    wb2.save('Sheet1test.xlsx')

append_row()