excel - 宏在 VBE 中正确运行，而不是从 Excel 文件中的加载项按钮

Question

我正在寻找有关以下代码的帮助。虽然它在 VB 编辑器中运行良好，但当我从 Excel 文件本身中添加的加载项按钮运行宏时，它会引发错误。它不保存新工作簿，而是尝试保存启用宏的原始工作簿。由于权限问题，我无法使用 SaveAs 方法，我通过提示用户选择文件路径来规避这个问题。任何帮助将非常感激！也请随时更正我下面的代码。

Sub Forecast()

Dim saveResult As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Define variable name of new workbook
NewWBname = "Revenue Forecast " & Format(Date, "yyyy-mm-dd")

'Copy data from macro-enabled file to new workbook
Sheets(Array("Revenue Forecast", "Probability Table")).Copy

'Rename $A$1
With ActiveWorkbook
    .Worksheets("Revenue Forecast").Range("A1").FormulaR1C1 = "Revenue Forecast " & Year(Now()) + 1
        'Autofit all columns for both sheets on new workbook
        For Each Wsht In Worksheets
            With Wsht.UsedRange
                .EntireColumn.AutoFit
            End With
        Next Wsht
    'Open Save As Dialogue Box to prompt user to choose file path
    saveResult = Application.Dialogs(xlDialogSaveAs).Show(NewWBname, Arg4:=xlOpenXMLWorkbook)

End With

End Sub

标签： excelvba