我在你的代码中看到了很多问题。
1.避免使用.Activate/.Select。您可能想查看How to avoid using Select in Excel VBA macros
1.使用对象。查看我如何声明工作表/范围/工作簿对象
这就是你要尝试的吗？

Sub ExportNameAndSave()
    Dim lRow As Long, lCol As Long
    Dim refnumber As String
    Dim ws As Worksheet
    Dim wb As Workbook

    '~~> Change this to the relevant sheet
    Set ws = ActiveSheet

    With ws
        '~~> Find last row and last column
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        refnumber = .Range("b4").Value

        '~~> Set your range
        Set rng = .Range(.Cells(1, 1), .Cells(lRow, lCol))

        '~~> Add a new workbook
        Set wb = Workbooks.Add

        '~~> Copy the range to sheet1 of new workbook
        rng.Copy wb.Sheets(1).Range(rng.Address)
    End With

    '~~> Save the new workbook
    wb.SaveAs Filename:="D:\Common Area\" & refnumber & ".xlsm", _
              FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
              CreateBackup:=False

End Sub

Sub ExportNameAndSave()

ActiveWindow.Activate
ActiveSheet.Select

Dim wkbk As Workbook
Dim lastrow As Range
Dim lastcolumn As Range
Dim refnumber As String

refnumber = Range("b4").Value

Range("A1", Range("a60000").End(xlUp)).Select
Set lastrow = Selection

Range("A1", Range("a1").Offset(0, 50).End(xlToLeft)).Select
Set lastcolumn = Selection

Range(lastrow, lastcolumn).Select

Selection.Copy
Set wkbkSource = Workbooks.Add

ActiveWorkbook.Activate
ActiveWorkbook.SaveAs Filename:="D:\Common Area\" & refnumber & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

然而，我发现了一个完美的工作方法来解决我的问题，如下所示：
SubExportNameAndSave（）
ActiveWindow.Activate
startingpoint = ActiveWorkbook.Name
ActiveSheet.Select
Dim lastrow As Range Dim lastcolumn As Range Dim refnumber As String
refnumber = Range（“b4”）。值
Range（“A1”，Range（“a60000”）.End（xlUp））.Select Set lastrow = Selection
Range（“A1”，Range（“a1”）.Offset（0，50）.End（xlToLeft））.Select Set lastcolumn = Selection
Range（lastrow，lastcolumn）.选择
Selection.Copy
Workbooks.Add
Dim checkedopenworkbook As Excel.工作簿
对于每个选中的打开工作簿在Excel中。工作簿

If checkedopenworkbook.Name = "PERSONAL.XLSB" Then

        Else

        If checkedopenworkbook.Name = startingpoint Then

        Else

        checkedopenworkbook.Activate

        If ActiveSheet.Range("a1").Value = "" Then

            checkedopenworkbook.Close

        Else

            checkedopenworkbook.Activate

            checkedopenworkbook.SaveAs Filename:="D:\Common Area\" & refnumber & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

        End If

    End If

    End If

下一个选中打开工作簿
End Sub

仅限于使用Workbook.Add命名Excel文件。我为此挣扎了一段时间，在Excel中向打开的文件中添加数字时存在问题。我的发现是：仔细阅读Workbook.ADD和Workbook.Open之间的区别。
ADD函数将文件名作为模板，并打开一个新文件。应该如何称呼：原始文件名+一个数字。
使用OPEN函数时，名称保持为您作为参数输入的名称。
使用ADD函数而不是OPEN函数看起来很自然，但唉。