VBA Code Hanging On One Line When In Personal.XLSB Workbook

[excelbytes]

I have a code that I found on line that takes an Excel file and splits it into multiple workbooks based on the criteria from one of the columns. It works fine when the code is in the workbook, but gets hung up on one of two lines when I try to run the code from my Personal.XLSB workbook. The part of the code where it snags is near the top:

Set wsSource = ThisWorkbook.Worksheets("Data")
Set wsHelper = ThisWorkbook.Worksheets("Helper")

Does it make sense that it won't work from a Personal.XLSB as compared to having the code in the file itself? How could I change these lines of code to address that?:

Option Explicit

Const Target_Folder As String = "C:\Users\mremp\OneDrive\Desktop\Split Files Test\"
Dim wsSource As Worksheet, wsHelper As Worksheet
Dim LastRow As Long, LastColumn As Long

Sub SplitDataset()
    
    Dim collectionUniqueList As Collection
    Dim i As Long
    
    Set collectionUniqueList = New Collection
    
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsHelper = ThisWorkbook.Worksheets("Helper")
    
    ' Clear Helper Worksheet
    wsHelper.Cells.ClearContents
    
    With wsSource
        .AutoFilterMode = False
        
        LastRow = .Cells(Rows.count, "A").End(xlUp).Row
        LastColumn = .Cells(1, Columns.count).End(xlToLeft).Column
        
        If .Range("A2").Value = "" Then
            GoTo Cleanup
        End If
        
        Call Init_Unique_List_Collection(collectionUniqueList, LastRow)
        
        Application.DisplayAlerts = False
        
        For i = 1 To collectionUniqueList.count
                SplitWorksheet (collectionUniqueList.Item(i))
        Next i
        
        ActiveSheet.AutoFilterMode = False
        
    End With

Cleanup:

    Application.DisplayAlerts = True
    Set collectionUniqueList = Nothing
    Set wsSource = Nothing
    Set wsHelper = Nothing

End Sub

Private Sub Init_Unique_List_Collection(ByRef col As Collection, ByVal SourceWS_LastRow As Long)
    
    Dim LastRow As Long, RowNumber As Long
    
    ' Unique List Column
    wsSource.Range("S2:S" & SourceWS_LastRow).Copy wsHelper.Range("A1")
    
    With wsHelper
        
        If Len(Trim(.Range("A1").Value)) > 0 Then
            
            LastRow = .Cells(Rows.count, "A").End(xlUp).Row
            
            .Range("A1:A" & LastRow).RemoveDuplicates 1, xlNo
            
            LastRow = .Cells(Rows.count, "A").End(xlUp).Row
            
            .Range("A1:A" & LastRow).Sort .Range("A1"), Header:=xlNo
            
            LastRow = .Cells(Rows.count, "A").End(xlUp).Row
            
            On Error Resume Next
            For RowNumber = 1 To LastRow
                col.Add .Cells(RowNumber, "A").Value, CStr(.Cells(RowNumber, "A").Value)
            Next RowNumber
           
        End If
    
    End With
    
End Sub

Private Sub SplitWorksheet(ByVal Category_Name As Variant)
    
    Dim wbTarget As Workbook
    
    Set wbTarget = Workbooks.Add
    
    With wsSource
        
        With .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
            .AutoFilter .Range("S1").Column, Category_Name
            
            .Copy
            
            'wbTarget.Worksheets(1).PasteSpecial xlValues
            wbTarget.Worksheets(1).Paste
            wbTarget.Worksheets(1).Name = Category_Name
            
            wbTarget.SaveAs Target_Folder & Category_Name & ".xlsx", 51
            wbTarget.Close False
            
        End With
        
    End With
    
    Set wbTarget = Nothing
    
End Sub

 

[Jeffrey Mahoney]

"ThisWorkbook" always refers to the workbook where the code resides. You want to use "ActiveWorkbook" instead

 

[excelbytes]

"ThisWorkbook" always refers to the workbook where the code resides. You want to use "ActiveWorkbook" instead

Thanks so much. I'll change it and give it a try!