1. 获取多有的sheets 并对sheet 循环处理
Dim sheets As sheets
Dim sheet As Worksheet
Dim name As String
Set sheets = ThisWorkbook.Worksheets
For Each sheet In sheets
If sheet.name <> "Sheet1" Then
name = sheet.name
End If
Next sheet
2. 添加一个工作薄 并将内容添加进去
Dim targetWb As Workbook
Set targetWb = Workbooks.Add
Dim targetWs As Worksheet
Set targetWs = targetWb.Worksheets(1)
'将其他表的数据复制到 新的工作薄的工作表里面
ws.Copy targetWs
targetWb.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbook ' 保存为.xlsx格式
For Each sht In targetWb.Worksheets
If InStr(UCase(sht.name), UCase("sheet")) > 0 Then
On Error Resume Next
sht.Delete
On Error GoTo 0
End If
Next
2. 读取工作薄中的工作表 并将含有公式的单元格 转化为文本
Set tatWb = Workbooks.Open(savePath)
Set sh = tatWb.Worksheets(name) ' 获取工作表的引用
Dim rngCell As Range
For Each rngCell In sh.UsedRange.SpecialCells(xlCellTypeFormulas)
rngCell.Value = rngCell.Value
Next rngCell
tatWb.Save
tatWb.Close