Question

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

新工作薄以原工作表名称命名

Answer 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

Answer 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