Where is 'Sub sys_PrepareToSend()' code located? It may be that your code is located on in your local hard drive under your profile similar to the following 'C:\Users\user_name\AppData\Roaming\Microsoft\AddIns\' rather than in the workbook?

I have something that does something similar, except that it copies and paste the content of the used range over the top of itself. Not exactly what you want, but may help. Careful as this wipes out all formulas so you must save the workbook to a different file before running.

'Step 3
private Sub copy_and_paste_values_in_order()
    Dim sht_order As Variant
    sht_order = Array("Sum2", "SPaint", "FPaint", "Supt", "Prod", "Pile", "Conc", "PipUG", "Steel", "Equip", "PipShp", _
                      "PipFld", "Insul", "Trace", "FirePrf", "EI", "Bldg", "Demo", "SpSub", "Indir", "Conting", "Owner", "KeyQty")

    'Dim variables used in the loop
    Dim ws As Worksheet
    Dim usedRange As Range
    Dim sht As Variant

    For Each sht In sht_order
        on Error Resume Next
        ' Set the worksheet object
        Set ws = ThisWorkbook.Sheets(sht)

        ' Define the used range
        Set usedRange = ws.usedRange

        ' Copy the used range
        usedRange.Copy

        ' Define the destination range (e.g., starting at cell A1 in a different location)
        ws.Range("A1").PasteSpecial Paste:=xlPasteValues

        ' Clear Clipboard (Optional)
        Application.CutCopyMode = False

        'Activate current Sheet, Select Cell A2, Scoll Up -> True
        ws.Activate
        ws.[a2].Select
        Application.Goto Worksheets(Sht).Range("A2"), True

    Next sht

    'cleanup delete ws object
    Set ws = Nothing
End Sub