如何用VBA引用以上月命名的Excel文件并粘贴数据？

核心错误原因 #

最后一行引用工作簿时，日期格式拼接逻辑错误：你将格式字符串错误写为"mmm yyyy" & "xlsx"，导致生成的文件名变成Malton Jan 2022xlsx（缺少后缀点号，且格式拼接位置错误），系统无法找到对应文件，触发下标越界。

另外直接通过文件名引用工作簿容易因名称变化（如后缀、大小写）出错，建议改用对象变量保存新建工作簿。

修正后的代码 #

Dim newWB As Workbook
Dim lastMonthName As String

' 生成标准格式的上月名称（示例：Jan 2022）
lastMonthName = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy")

' 打开模板并将工作簿对象赋值给变量
Set newWB = Workbooks.Open(Filename:="https://X.sharepoint.com/GROUP/Whiteboard/Malton%20New%20Month%20Template.xlsx?web=1")

' 另存为以上月名称命名的文件（明确添加.xlsx后缀）
newWB.SaveAs Filename:="https://X.sharepoint.com/GROUP/Whiteboard/2022/Malton/Malton " & lastMonthName & ".xlsx", _
    FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

' 复制源数据
ThisWorkbook.Worksheets("Malton Weekly Input").Range("A5:R404").Copy

' 粘贴到新工作簿指定区域（仅粘贴值）
newWB.Worksheets("Data").Range("AC5:AT404").PasteSpecial Paste:=xlPasteValues

' 清理剪贴板，释放系统资源
Application.CutCopyMode = False

关键优化说明 #

• 使用Dim newWB As Workbook保存新建工作簿的对象引用，彻底避免因文件名拼写、ActiveWorkbook切换导致的下标越界问题
• 单独生成上月名称字符串，减少重复计算，提升代码可读性
• 保存文件时明确添加.xlsx后缀，确保文件名格式完全符合系统识别要求
• 用ThisWorkbook替代Workbooks("WHITEBOARD project.xlsm")，更可靠（指向当前运行代码的工作簿，不受文件名修改影响）

内容的提问来源于stack exchange，提问作者ALanders