以下代码存为宏,另从为启用宏的工作表(xlsm格式),处理你的表格的时候保证此工作表打开。
在“开发工具”选项卡,“插入”-“按钮”,指定Save_As()这个宏。
开发工具选项卡默认不显示,怎么显示自行百度。
Sub Save_As()
On Error GoTo error_shoot
Dim S_path, Old_Path As String
Old_Path = ActiveWorkbook.FullName
With ActiveWorkbook.ActiveSheet
If .Cells(1, 1) & .Cells(4, 1) & .Cells(3, 2) = "" Then
MsgBox "文件名为空"
Exit Sub
End If
If IsError(.Cells(1, 1) & .Cells(4, 1) & .Cells(3, 2)) = True Then
MsgBox "文件名存在错误值"
Exit Sub
End If
'"D:\
新建文件夹"是目标另存路径,请自行修改,请提前创建文件夹,否则会报错
S_path = "D:\新建文件夹" & "\" & .Cells(1, 1) & "-" & .Cells(4, 1) & "-" & .Cells(3, 2)
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=S_path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
ActiveWorkbook.SaveAs Filename:=Old_Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox S_path & Chr(10) & "文件已创建"
Exit Sub
error_shoot:
Application.DisplayAlerts = True
MsgBox "出错了,请检查目标文件夹权限,并确保目标文件夹中同名文件未被打开。"
End Sub