☰

根据名称保存工作表

tag-icon • tag-icon microsoft-excel vba
根据名称保存工作表

这个宏几乎完全满足了我的需要，但我希望它在每个工作表名称末尾包含“_sspl.xlsx”，并将文件放在与原始文件路径同名的文件夹中。

我似乎做不到正确。

Sub CreateNewFolder()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

    Set wbThis = ThisWorkbook.Name
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

答案1

“我似乎无法做到这一点”并不是对问题最准确的描述:)

以下代码未经测试（我这台机器上没有 Excel），但我思考它会做你想做的事。

Sub CreateNewFolder()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet 
Dim strFilename As String

Set wbThis = ActiveWorkbook
For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name & "_sspl.xlsx"
    ws.Copy
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs Filename:=strFilename
    wbNew.Close
Next ws

End Sub

相关内容