Macro shared with multiple users will not copy/paste into workbook

Question

Myself and several coworkers format reports from exported data on a biweekly basis. I want to create a macro that I can distribute to everyone in which all they do is run the macro in as little steps as possible on the data they exported to their local machine. I created a macro, but I cannot get it to work because the destination workbook (ThisWorkbook.Activesheet), is my "PERSONAL.XLSB" (which is located in "XLSTART" folder and hosts the macro)and not the workbook that it needs to (which will change with every person, each week). The main problem is that whenever I run the macro, the data from "END_OF_REPORT.xlsx" will not copy and paste because it is trying to paste to "PERSONAL.XLSB".

For example, I am location 2, sally is location 3. We both need to run reports for jan 1-jan 7, so the file (workbook) that will be ran would be "jan1-jan7_1.xlsx" for my location, and "jan1-jan7_2.xlsx" for sally's location, and both will include only "Sheet1".

when I open visual basic, the projects that are opened are "VBAProject (JAN1-JAN7_2.xlsx)", and "VBAProject (PERSONAL.XLSB)" and under that is Module 1, Module 2, Sheet1, ThisWorkbook. Module 1 is for location 1, Module 2 is for location 2. Whenever I try to run the appropriate macro, it will do everything but copy and paste the data from the workbook that I open and close within the macro.

Sub LOCATION_ONE_TRANS()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
    
    Workbooks.Open "\\FILEPATH\END_OF_REPORT.xlsx"
       
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim CopyLastRow As Long
    Dim DestLastRow As Long
    
    'Replace 1 with appropriate location
    Set wsCopy = Workbooks("END_OF_REPORT.xlsx").Worksheets("1")
    Set wsDest = ThisWorkbook.ActiveSheet

    CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    DestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

    wsCopy.Range("A1:E" & CopyLastRow).Copy _
        wsDest.Range("A" & DestLastRow)
    Workbooks("END_OF_REPORT.xlsx").Close SaveChanges:=False

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _
        ("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Cells.Select
    
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
...
...
    Dim rangeSelection As Range
    Dim cellCurrent As Range
 
    Set rangeSelection = Application.Selection.Columns(1).Cells
    ActiveSheet.ResetAllPageBreaks
 
    For Each cellCurrent In rangeSelection
        If (cellCurrent.Row > 1) Then
            If (cellCurrent.Value <> cellCurrent.Offset(-1, 0).Value) Then
                ActiveSheet.Rows(cellCurrent.Row).PageBreak = _
                    xlPageBreakManual
            End If
        End If
    Next cellCurrent
    Selection.RowHeight = 36.6
    Cells.EntireColumn.AutoFit
    Application.WindowState = xlMaximized
    ActiveWindow.Zoom = 50
    ActiveSheet.Range("A1").Select
    ActiveWorkbook.Save
    
End Sub

What should happen is I share the Macro with my coworkers so that they just need to open up the workbook they need to run the macro on, enable macro because they saved this macro to their "XLSTART", and then run the macro. What is actually happening is when anyone runs the macro, the worksheet is formatting but the copy/pasted data is not making it onto the correct workbook (ie, JAN1-JAN7_2.xlsx)

Answer 1

ThisWorkbook always refers to the file where the code is located. That's why the line Set wsDest = ThisWorkbook.ActiveSheet will set your Personal.xlsb as the destination.