|
本帖最后由 hahake402 于 2019-12-26 20:39 编辑
拆分表格写了一段,能够做相应的动作,但是会报1004错误
提示语句是:Sheets(Sheets.Count).Name = Sheet1.Cells(i, l) 1004错误.
我用的是 2016 版
程序如下:
Sub chaifen()
Dim sht, sht1 As Worksheet
Dim i, j, k As Integer
Dim irow As Integer '表里数据有多少行
Dim l As Integer '输入列的数值
l = InputBox("希望按第几列分")
'因为输入的是数字 rang要用字母表示,所以用cell 代替range
'删除其他表
If Sheets.Count > l Then
Application.DisplayAlerts = flase
For Each sht1 In Sheets
If sht1.Name <> "MAF" Then
sht1.Delete
End If
Next
Application.DisplayAlerts = ture
End If
'表里数据有多少行
irow = Sheet1.Range("a65536").End(xlUp).Row
'拆分表
For i = 1 To irow
k = 0 '恢复初始值
For Each sht In Sheets
If sht.Name = Sheet1.Cells(i, l) Then
k = 1 '表示表名字已存在
End If
Next
If k = 0 Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Cells(i, l) '会报1004错误
'另一个方法以上两行合并成一行sheets.add (after:=sheets(sheets.count)).name = "123"
End If
Next
'拷贝数据
For j = 2 To Sheets.Count
Sheet1.Range("a1:f" & irow).AutoFilter Field:=l, Criteria1:=Sheets(j).Name
Sheet1.Range("a1:f" & irow).Copy Sheets(j).Range("a1")
Next
Sheet1.Range("a1:f" & irow).AutoFilter
Sheet1.Select
MsgBox "已处理完毕!"
End Sub
|
|