Why do i get run-time error 9: subscript out of range error?

So I've macros that work perfectly when I copy and paste it into the developer tool. But instead of doing that to each new excel sheet I open, I want to have it open in the background in the setting Macros in 'All Open Workbooks'. But I get the titled error instead. Here are the macros:

Macro 1:

Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        
    Dim lastRow As Long
    Dim cell As Range
    Dim insertRow As Long
    Dim insertColumn As Long
    
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    For Each cell In Range("C1:C" & lastRow)
        If InStr(cell.Value, "Level") > 0 Then
            insertRow = cell.Row
            insertColumn = cell.Column
            Range(Cells(insertRow, insertColumn - 1), Cells(insertRow, insertColumn - 1)).Value = 1
            Range(Cells(insertRow + 1, insertColumn - 1), Cells(insertRow + 1, insertColumn - 1)).Value = 1
        End If
    Next cell
    
    Columns("K:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
        OtherChar:="|", FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(16, 1)), _
        TrailingMinusNumbers:=True
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Delete Shift:=xlToLeft
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft


    Dim r As Range, x, w, i As Long
    Set r = Columns("c").Find("Item", , , 2)
    If r Is Nothing Then Exit Sub
    w = Split(Split(Split(Application.Trim(r), "Standard")(0), ": ")(1), " ", 2)
    x = Filter([transpose(if((left(trim(d1:d10000),15)="Operation Costs")+(left(trim(d1:d10000),15)="Total Operation"),row(1:10000)))], False, 0)
    For i = 0 To UBound(x) - 1
        If (Trim$(Cells(x(i), "d")) Like "Operation Costs*") * (Trim$(Cells(x(i + 1), "d")) Like "Total Operation*") Then
            With Cells(x(i) + 5, 1).Resize(x(i + 1) - x(i) - 6, 3)
                .Columns(1) = Cells(x(i), 3)
                .Columns("b:c") = w
            End With
        End If
    Next
End Sub

Macro 2:

Sub Autopopulate()

    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim i As Long
    Dim j As Long
    Dim sourceCell As Range
    Dim destinationCell As Range
    
    ' Set the source sheet
    Set sourceSheet = ThisWorkbook.Sheets("ticpr2420m000 Data Dump") ' Replace "ticpr2420m000 Data Dump" with the name of your source sheet
    
    ' Set the destination sheet
    Set destinationSheet = ThisWorkbook.Sheets("Routing") ' Replace "Routing" with the name of your destination sheet
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
    
    ' Initialize destination row
    destRow = 2
    
    ' Loop through each row in the source sheet
    For i = 1 To lastRow
        ' Check if column A in the current row is not empty
        If Not IsEmpty(sourceSheet.Cells(i, 1).Value) Then
            ' Loop through each cell in the current row (excluding column K)
            For j = 1 To sourceSheet.Cells(i, sourceSheet.Columns.Count).End(xlToLeft).Column
                ' Exclude column K
                If j <> 11 Then
                    Set sourceCell = sourceSheet.Cells(i, j)
                    Set destinationCell = destinationSheet.Cells(destRow, j)
                    
                    ' Copy the value from the source cell to the destination cell
                    destinationCell.Value = sourceCell.Value
                End If
            Next j
            
            ' Increment destination row
            destRow = destRow + 1
        End If
    Next i
    
 ' UnhideAllRows()
    
    ' Set the destination sheet
    Set destinationSheet = ThisWorkbook.Sheets("Routing") ' Replace "Routing" with the actual name of your destination sheet
    
    ' Clear any existing filters
    destinationSheet.AutoFilterMode = False
    
    ' Find the last row in the sheet
    lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row
    
    ' Unhide all rows in the sheet
    destinationSheet.Rows("1:" & lastRow).Hidden = False


    ' Clear contents of column H in destination sheet
    destinationSheet.Range("H2:H" & destRow - 1).ClearContents

    'Routing to OP
    
    Dim ws As Worksheet
    Dim rng As Range
    Dim filterRange As Range
    Dim filterCriteria As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Routing")

    ' Determine the last row in the sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Set the range to be filtered (Columns A to V)
    Set rng = ws.Range("A1:V" & lastRow)

    ' Clear any existing filters
    ws.AutoFilterMode = False

    ' Set the filter range to include all columns from A to V (including the header row)
    Set filterRange = rng

    ' Set the filter criteria to filter values starting with a space in column F
    filterCriteria = " =*"

    ' Apply the filter to column F
    filterRange.AutoFilter Field:=6, Criteria1:=filterCriteria

    ' Component paste
    
    Dim maxRows As Long
    
    ' Set the source and destination sheets
    Set sourceSheet = ThisWorkbook.Sheets("ticpr2420m000 Data Dump") ' Change "Sheet1" to the name of your source sheet
    Set destinationSheet = ThisWorkbook.Sheets("Components") ' Change "Sheet2" to the name of your destination sheet
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize the destination row counter
    destRow = 2 ' Start pasting from row 2 in the destination sheet
    
    ' Calculate the maximum number of rows that can be copied without exceeding the destination sheet's capacity
    maxRows = destinationSheet.Rows.Count - destRow + 1
    
    ' Loop through each row in the source sheet
    For i = 1 To lastRow
        ' Check if the first two cells are empty and cell I has data
        If IsEmpty(sourceSheet.Cells(i, 1).Value) And IsEmpty(sourceSheet.Cells(i, 2).Value) And Not IsEmpty(sourceSheet.Cells(i, 9).Value) Then
            ' Copy the specified range of columns to the destination sheet
            sourceSheet.Range("C" & i & ":K" & i).Copy destinationSheet.Range("C" & destRow)
            destRow = destRow + 1 ' Increment the destination row counter
            
            ' Check if the maximum number of rows has been reached
            If destRow > (maxRows + 1) Then
                Exit For
            End If
        End If
    Next i
    
    ' Clear content below the last pasted row in the destination sheet
    destinationSheet.Range("C" & destRow & ":K" & destinationSheet.Rows.Count).ClearContents
    
    ' Autofit columns in the destination sheet
    destinationSheet.UsedRange.Columns.AutoFit
    
    ' Notify user that the operation is complete
    MsgBox "Rows copied successfully!", vbInformation


    '  Copycomptobom
    
    ' Set the source sheet
    Set sourceSheet = ThisWorkbook.Sheets("Components") ' Replace "Components" with the actual name of the source sheet
    
    ' Set the destination sheet
    Set destinationSheet = ThisWorkbook.Sheets("BOM") ' Replace "BOM" with the actual name of the destination sheet
    
    ' Copy data from source sheet to destination sheet
    sourceSheet.Range("C2:K12").Copy
    destinationSheet.Range("C5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    ' Clear clipboard
    Application.CutCopyMode = False
End Sub

Thanks!