VBA可以通过Worksheet.Copy函数,把工作表复制到单独的工作簿。
需求分析
可以遍历当前工作簿的所有工作表,并将工作表另存为新的工作簿,新的工作簿以工作表的名字命名,保存到指定目录中。
实现
'
' 复制工作表到单独的工作簿
'
Sub copyWorksheetsToWorkbook()
Dim wb As Workbook, sht As Worksheet, curPath As String, copyToPath As String
'
curPath = ThisWorkbook.Path
' 复制到的目录
copyToPath = curPath & "\copies\"
If Dir(copyToPath, vbDirectory) = "" Then
MkDir copyToPath
End If
'
Set wb = ThisWorkbook
For Each sht In wb.Worksheets
' 复制当前工作表到新的工作簿
sht.Copy
' 保存新的工作簿
Set wb = ActiveWorkbook
wb.Close True, copyToPath & sht.Name & ".xlsx"
Set wb = Nothing
Next
Set ws = Nothing
Set wb = Nothing
MsgBoxEx "所有工作表导出完毕。即将打开目录"
' 打开目录
OpenFolderInExplorer copyToPath
End Sub