|
Sub 去除公式连接()
Dim i As Integer
Dim y As Integer
Application.ScreenUpdating = False
y = InputBox("请输入表格数量")
For i = 2 To y + 1
Sheets(i).Select
Columns("A:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:D").Select
Range("D1").Activate
Selection.Delete Shift:=xlToLeft
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft
Sheets(i).Copy
ActiveWorkbook.SaveAs Application.ActiveWorkbook.PATH & "\" & Sheets("i").Name & ".xls" '(工作表名称为文件名)
ActiveWorkbook.Close
Next
Application.ScreenUpdating = True
End Sub
本帖最后由 hwc2ycy 于 2012-10-22 18:11 编辑
- Sub 去除公式连接()
- Dim i As Integer
- Dim sFilename
- Dim y As Integer
- Application.ScreenUpdating = False
- '关闭警告信息和对话框,默认情况下重名文件会被覆盖
- Application.DisplayAlerts = False
- ' 跳过错误
- On Error Resume Next
- '检测输入值是否合法
- y = CInt(InputBox("请输入表格数量"))
- If y > Worksheets.Count Or y = 0 Then
- MsgBox "请输入正确的工作表数量" & "[<" & Worksheets.Count & "]"
- Exit Sub
- End If
- For i = 2 To y + 1
- Sheets(i).Select
- sFilename = ThisWorkbook.Path & Application.PathSeparator & Sheets(i).Name & ".xls"
- 'sfilename= ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Sheets(i).Name & ".xls"
- Columns("A:M").Select
- Selection.Copy
- Selection.PasteSpecial Paste:=xlPasteValues
- Application.CutCopyMode = False
- Columns("A:D").Select
- Range("D1").Activate
- Selection.Delete Shift:=xlToLeft
- Columns("J:K").Select
- Selection.Delete Shift:=xlToLeft
- Sheets(i).Copy
-
- 'ActiveWorkbook.SaveAs Application.ActiveWorkbook.Path & "" & Sheets("i").Name & ".xls" '(工作表名称为文件名)
- Debug.Print sFilename
- ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:=xlExcel7
- 'ActiveWorkbook.Close True, Filename:=sFilename
- ActiveWorkbook.Close
- Next
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|