Excel for Macでは、ピボットテーブルを作成する際に複数テーブルを一括指定できないため結構不便です。（古いバージョンではできていたはず…）
まずは、pythonで複数シートのデータを1つシートにまとめてデータを扱いやすくしていきます。

#使用ライブラリ
openpyxlを使用します。
Excelの読み書きなどの操作を行うためのライブラリです。
https://openpyxl.readthedocs.io/en/stable/

#事前準備
openpyxlのインストール

pip install openpyxl

#ファイルの読み込み

#ファイルの読み込み
read_path = "./input.xlsx"
output_path = "./output.xlsx"
wb = load_workbook(read_path)

#全シートのデータを取得する

#全シートのデータを取得する
sheet_names = wb.get_sheet_names()
first_row = 1
l_2d_output = []
for sheet_name in sheet_names:
    sheet = wb.get_sheet_by_name(sheet_name)
    
    for row in sheet.iter_rows(min_row=first_row):
        l = []
        for cell in row:
            l.append(str(cell.value))
        l_2d_output.append(l)
        
    #次シートからヘッダーの読み込みを飛ばす
    first_row = 2

#書き込み

#ファイルに書き込み
def write_list_2d(sheet, l_2d, start_row, start_col):
    for y, row in enumerate(l_2d):
        for x, cell in enumerate(row):
            sheet.cell(row=start_row + y,
                       column=start_col + x,
                       value=l_2d[y][x])

#書き込み
output_wb = openpyxl.Workbook()
output_ws = output_wb.active
write_list_2d(output_ws, l_2d_output, 1, 1)
output_wb.save(output_path)

#最後に
改善の余地がありすぎるコードですが、
自分が使う分には使えたので、いったん良しとします。