title: Issue with splitting Excel file (.xlxs) with Powershell based on column values
body: <p>I have the below script that was posted here: <a href="https://stackoverflow.com/questions/60060404/split-excelfile-xlxs-with-powershell-based-on-column-values/69578587#69578587">Split Excelfile .xlxs with Powershell based on column values</a> and it works as described but I am experiencing an issue.</p>
  <p>The script will check one xlsx file and sort the content by unique values in column A and then copy these data set and create a new file.</p>
  <p>The issue I am having is out of about 4000 files, 20 of them are created with the full data set from the original file. The rest of the files a split and created correctly. Not sure as to why this is occurring. Any help is appreciated.</p>
  <pre><code>Function Create-Excel-Spreadsheet {
  Param($NameOfSpreadsheet)
  
  # open excel
  $excel = New-Object -ComObject excel.application
  $excel.visible = $true
  
  # add a worksheet
  $workbook = $excel.Workbooks.Add()
  $xl_wksht= $workbook.Worksheets.Item(1)
  $xl_wksht.Name = $NameOfSpreadsheet
  
  return $workbook
  }
  
  
  $objexcel = New-Object -ComObject Excel.Application
  $wb = $objexcel.WorkBooks.Open(&quot;C:\Temp\Test.xlsx&quot;) # Changing path for test.xlsx file.
  
  $objexcel.Visible = $true
  $objexcel.DisplayAlerts = $False
  $ws = $wb.Worksheets.Item(1)
  
  $usedRange = $ws.UsedRange
  $usedRange.AutoFilter()
  
  $totalRows = $usedRange.Rows.Count
  
  
  $rangeForUnique = $usedRange.Offset(1, 0).Resize($UsedRange.Rows.Count-1) 
  [string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | sort -Unique
  
  for ($i = 0; $i -lt $UniqueListOfRowValues.Count; $i++) {
      $newRange = $usedRange.AutoFilter(1, $UniqueListOfRowValues[$i])
  
      $workbook = Create-Excel-Spreadsheet $UniqueListOfRowValues[$i]
      $wksheet = $workbook.Worksheets.Item(1)
  
      $range = $ws.UsedRange.Cells
      $range.Copy()
  
      $wksheet.Paste($wksheet.Range(&quot;A1&quot;))
      $workbook.SaveAs(&quot;C:\temp\&quot; + $UniqueListOfRowValues[$i], $xlFixedFormat)
      $workbook.Close()
  }
  </code></pre>