Using an array value to sort a row to a specific Worksheet

[MoshiM]

I've recently tried to make a switch towards using for loops as opposed to just writing out all the code but I have run into an issue when using an array to sort rows based on an array value.
I have a table containing all the worksheet names and variables I wish to sort by. The data that needs to be sorted comes from a refreshable Web Query and I have merged my table with via Power Query so that all the relevant data needed for sorting is always in the same row.
I'm currently getting a subscript out of range error on this line:

If ThisWorkbook.Worksheets("Weekly").Cells(y, 33).Value = ContractCodes(i) Then

I'm also fairly sure that

ThisWorkbook.Worksheets(WS_Name(i))

will result in another error.
Any help you can provide would be greatly appreciated

Sub Sort_L()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual




Dim ContractCodes() As Variant
Dim WS_Name() As Variant


Dim i As Long


a = ThisWorkbook.Worksheets("Weekly").Cells(Rows.Count, 1).End(xlUp).Row    'find the last used row number on Worksheet "Weekly"
ReDim ContractCodes(0 To a - 1)                                             'Size of the Array
ReDim WS_Name(0 To a - 1)                                                   'Size of the Array
WS_Name = Range(Cells(2, "AK"), Cells(a, "AK"))                             'First row has headers so columns start at 2
ContractCodes = Range(Cells(2, "AJ"), Cells(a, "AJ"))                       'Contract Codes are what each row will be sorted by


For y = 2 To a                                                              'From the Second Row to the Bottom of the used rows
    For i = LBound(ContractCodes) To UBound(ContractCodes)
'variable i used for both ContractCodes and WS_Name because arrays will always be the same size along with the corresponding values being in the same row
        If ThisWorkbook.Worksheets("Weekly").Cells(y, 33).Value = ContractCodes(i) Then
            d = ThisWorkbook.Worksheets(WS_Name(i)).Cells(Rows.Count, 1).End(xlUp).Row
            ThisWorkbook.Worksheets(WSName(i)).Cells(d + 1, 1).Value = Worksheets("Weekly").Range(Cells(y, "A"), Cells(y, "AG")).Value


         End If
    Next i
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Call ToTheHub
End Sub

 

[MoshiM]

I managed to figure it out. Here is the solution in case someone else tries to do the same

Sub Sort_L()


Dim ContractCodes() As String
Dim WS_Name() As String
Dim j As Long


a = ThisWorkbook.Worksheets("Weekly").Cells(Rows.Count, 1).End(xlUp).Row 'find the last used row number on Worksheet "Weekly"


ReDim ContractCodes(1 To a - 1)
ReDim WS_Name(1 To a - 1)


j = 1
For f = 2 To a


    WS_Name(j) = ThisWorkbook.Worksheets("Weekly").Cells(f, "AH").Value
    ContractCodes(j) = ThisWorkbook.Worksheets("Weekly").Cells(f, "AG").Value
    j = j + 1
Next


For y = 2 To a
    For c = LBound(ContractCodes) To UBound(ContractCodes)


        If ThisWorkbook.Worksheets("Weekly").Cells(y, 33).Value2 = ContractCodes(c) Then
    
            d = ThisWorkbook.Worksheets(WS_Name(c)).Cells(Rows.Count, 1).End(xlUp).Row


            Range(ThisWorkbook.Worksheets(WS_Name(c)).Cells(d + 1, 1), ThisWorkbook.Worksheets(WS_Name(c)).Cells(d + 1, "AG")).Value2 = Range(ThisWorkbook.Worksheets("Weekly").Cells(y, "A"), ThisWorkbook.Worksheets("Weekly").Cells(y, "AG")).Value2
        End If
    
   Next c
Next
'For every row check if ContractCode is equal to a column value. If equivalent then set that rows contents equal to the range at the bottom of the corresponding Worksheet.


End Sub