Issue with Copying Data from 100 Excel sheets into a Single Sheet

I am using the Powershell script to load data from about 5 Excel sheets (as a sample) from column A to AL. In reality, I have 100 Excel Sheets with 25 Tabs in each file.

I have used the link How to use powershell to copy several excel worksheets and make a new one?

And it has been of great help. However, data from the first sheet is copied and entered into the new sheet but from the 2nd File onwards it starts giving the errors as below.

The 2nd loop should go to Last Used Row of the New Excel Sheet (row 151) and paste data from the 2nd Sheet. However I run into the error "Unable to get the Select property of the Range class". Then the data from the 2nd Sheet never gets pasted.

So out of the 5 sheets data from 2 sheets gets copied while the remaining data from the other 3 sheets never gets pasted onto the new Excel Sheet mainly due to the error "Unable to get the Select property of the Range class"

Error As Received

2nd Loop
151
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
151
2nd Loop
Destination UsedRange Row: - 311
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
Destination UsedRange Row: - 311
Unable to get the Select property of the Range class
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:84 char:51
+ ... t.Range("A$(($objDestExcel.ActiveSheet.UsedRange.Rows|Select -Last 1) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
At C:\Users\Desktop\ExcelCopy_ETC 2.ps1:70 char:5
+     $SourceWorksheet = $SourceWorkBook.WorkSheets.item("3. Automation ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

2nd Loop
Destination UsedRange Row: - 311
0