title: Merge same name sheets from various excels into one
body: <p>I am trying to merge sheets in multiple excels that have the same name into one excel sheet</p>
  
  <p>Have tried the code below:</p>
  
  <pre><code>import os
  import pandas as pd
  
  files = os.listdir("XXXX")
  
  print("All files in the given directory are :", files)
  
  files_xlsx = [f for f in files if f[-4:] == 'xlsx']
  
  print("Excel files in this directory are :", files_xlsx)
  
  y = "XXXX"
  
  excels = [pd.ExcelFile(y +'\\'+ name) for name in files_xlsx]
  
  for each in excels:
      sheets =pd.ExcelFile(each).sheet_names
  
  print ("Sheet names in these excel files are : ", sheets)
  
  NSheets = len(sheets)
  
  print ("Number of different Sheet names in these excel files are : ", NSheets)
  
  for z in sheets:
          frames = [x.parse(x.sheet_names[i], header=None,index_col=None) for x in excels]
          frames[1:] = [df[1:] for df in frames[1:]]
          combined = pd.concat(frames)
          combined.to_excel("XXXX\\Output_" + z + ".xlsx", header=False, index=False)
  </code></pre>
  
  <p>This results in multiple files created with different sheet names but all those sheets have the same data as in sheet 1. The data is not coming right.</p>