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!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have done this for you to keep things moving along.

When you are asking about a VBA error always indicate which line of code causes the error.

Every error is line related. It is not possible to help you diagnose this error without knowing what line of code caused it. This error can be caused by multiple problems and I can't go through your code line-by-line to try to guess what it is.

This error can be caused by using Worksheets() or Sheets() referring to a worksheet name that does not exist. It is possible that is what is happening in your code. But without more information it's just a guess.

when another workbook tries to use it

I don't understand what you mean by this. Do you mean that code in another workbook that calls the code in this one? Or did you copy this code to a different workbook?

This is the last guess I am going to make without knowing which line of code causes the error. The workbook containing this code must have a worksheet named "Routing". Is that true when this error occurs?

Originally Posted by skhande2

when I execute it on a different workbook like this:
ibb.co/ DrDpTnm- remove space to see the picture.

You can upload images directly to a post, which saves steps over uploading to a third-party site then including a non-working link to it.

runmacro.jpg

ThisWorkbook refers to the workbook with the code in it. Perhaps you should try using ActiveWorkbook instead.

Originally Posted by skhande2

To use active workbook, I would need to copy paste the macro to the new workbook each time.

No, you would not. It will refer to whatever workbook is active when the code is running, no matter which workbook the code is in. ThisWorkbook by contrast, as Trevor said, always refers to the workbook with the code in it.

There are three ways to refer to a workbook:

1) As ThisWorkbook. As already noted, this ALWAYS refers to the workbook that contains the code. It sounds like you want the code stored in a common workbook, so that the code will never really work on the workbook that contains the code.
2) As ActiveWorkbook. This ALWAYS refers to the currently active workbook. This does not mean that the code must be housed in the active workbook; only that the workbook must be active at the time of execution. You can use ActiveWorkbook as long as you can be confident that you and your users will have the desired workbook open when executing the code.
3) As a named member of the Workbooks() collection. When you do not want to ALWAYS work on ThisWorkbook and cannot be certain that ActiveWorkbook will ALWAYS be the correct workbook, then you will need to use the Workbooks() collection to access the desired workbook. If you know the name of the workbook will always be the same, you can hardcode the name into the procedure. Or you can use an inputbox to have the user enter the name, or any of a number of other strategies for getting the desired workbook name so your procedure can access the desired workbook.

Currently, your code is using ThisWorkbook, which seems like the wrong approach for what you want.

I think many programmers would design this procedure to work on ActiveWorkbook with an error trap that traps the "wrong workbook open and active at time of execution" error with a message that the user should be sure that the correct workbook is open and active before executing the procedure and then terminating the procedure. This seems like it could easily be made to work for a "when I open a new workbook [so it should be the ActiveWorkbook], I can just execute [this procedure being stored in another workbook] with a shortcut" kind of scenario.

If you decide that you don't want to use either ThisWorkbook or ActiveWorkbook, then you as the programmer need to figure out how you would like the procedure to know what workbook it should be working on. Then add the appropriate lines of code to the procedure.

How would you like to proceed?

You can also refer to workbooks using the Workbooks Collection Index. Maybe not appropriate here but you can loop through the collection checking the workbook names.

I think the consensus is to refer to the Active Workbook, that is, the workbook that is selected and which you are working on, or wish to operate on. ThisWorkbook would be the "library" for the code and sitting in the background. This would need to be opened first so that the code is available for other workbooks.

Might have missed something but I don't think we've seen an example workbook where this works and one where it doesn't. Difficult to come up with constructive suggestions without the context.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

You cannot "save a macro as all open workbooks". A macro exists in one workbook (but can affect many). As written, your code will only ever work on the workbook containing the code because it refers to ThisWorkbook, not ActiveWorkbook.