Convert table data into list - VBA code

Dear all,
I am using the following code below, which extracts the prices according to names in column D and then totals all prices at the last row.

Option Explicit

Public Sub splitSortPivot()
Dim ws As Worksheet
Dim sourceRange As Range
Dim rng As Range
Dim i, j, k As Integer
Dim sourceArray As Variant
Dim arrangersArray As Variant
Dim ary As Variant

Set ws = Worksheets("Current_progress")
Set sourceRange = ws.Range("C2:D100")

'Based on the number of records +2 you have in source sheet (Sheet1)
'you may set the first dimension's upper bound of the array
'+1 for header and +1 for total --> in current case 12  + 1 + 1 = 14
'even setting this number can be done programmatically using used rows in C column.
ReDim arrangersArray(0 To 100, 5)

arrangersArray(0, 0) = "JPM"
arrangersArray(0, 1) = "CITG"
arrangersArray(0, 2) = "BAML"
arrangersArray(0, 3) = "BCG"
arrangersArray(0, 4) = "CIBC"
arrangersArray(0, 5) = "DB"

sourceArray = sourceRange.Value

    For j = LBound(sourceArray, 1) To UBound(sourceArray, 1)
        If InStr(1, sourceArray(j, 2), ",") > 0 Then
            ary = Split(sourceArray(j, 2), ",")
            For k = LBound(ary) To UBound(ary)
                For i = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                    If arrangersArray(0, i) = Trim(ary(k)) Then
                       arrangersArray(j, i) = sourceArray(j, 1)
                       arrangersArray(100, i) = arrangersArray(100, i) + arrangersArray(j, i)
                    End If
                Next i
            Next k
        Else
            For k = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                If arrangersArray(0, k) = sourceArray(j, 2) Then
                    arrangersArray(j, k) = sourceArray(j, 1)
                    arrangersArray(100, k) = arrangersArray(100, k) + arrangersArray(j, k)
                End If
            Next k
        End If
    Next j

'Output the processed array into the Sheet.
Range("G1").Resize(UBound(arrangersArray) + 1, _
UBound(Application.Transpose(arrangersArray))) = arrangersArray

End Sub

I require assistant in making the code to display the output in Sheet2 instead of in Sheet1.

I have attached a sample workbook showing:
Worksheet names “Current_progress” displays input and current output
Worksheet “Sheet1” shows input data
Worksheet “Sheet2” shows desired output from sheet 1 input.

Total_Count_Example_2611.xlsm

Any help would be very much appreciated. Thank you in advance.

Kind regards

Change this line

Range("G1").Resize(UBound(arrangersArray) + 1, _
UBound(Application.Transpose(arrangersArray))) = arrangersArray

in to

sheets("Sheet2").Range("G1").Resize(UBound(arrangersArray) + 1, _
UBound(Application.Transpose(arrangersArray))) = arrangersArray

Thank you so much for your response.

I would also like to ask, is it possible to make row 1 in sheet2, automated, for example to extract the names and prices from sheet1 automatically in a list format instead of manually adding it in.

thank you for your help and time.