如何用vba批量将一个excel文件中的多个工作表另存为指定路径的新工作薄,且只存数值不存公式和宏

新工作薄以原工作表名称命名
2024-10-31 07:33:20
有2个网友回答
网友(1):

打开这个excel ,另存为副本, (最好放到某个文件夹内)
按alt+F11, 视图---代码窗口 --把如下复制进去--按F5 运行即可

Sub fencun()
Application.ScreenUpdating = False
b = Sheets.Count

For i = b To 1 step -1
sheets(i).select
sheets(i).Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Sheets(i).Copy
a = ThisWorkbook.Worksheets(i).Name
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & a & ".xlsx"
.Close

End With
Next i

Application.ScreenUpdating = true

End Sub

网友(2):

Sub 拆分()
Dim Sht As Worksheet
iPath = "d:\pic" '保存的路径
For Each Sht In Sheets
Sht.UsedRange.Copy
Workbooks.Add
With ActiveWorkbook
.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
.SaveAs iPath & "\" & Sht.Name
.Close True
End With
Next
End Sub