|
ReDim arr3(1 To UBound(arr), 1 To 19)
With Sheet10
For i = 1 To r
MsgBox Sheet10.Cells(i, 14).Value
If Sheet10.Cells(i, 14).Value = "11" Then
m = m + 1
Sheets(Sheet16).Range("a&m:a&19") = Sheet10.Range("a&i:a&19")
End If
If Sheet10.Cells(i, 14).Value = "22" Then
n = n + 1
Sheets(Sheet17).Range("a&n:a&19") = Sheet10.Range("a&i:a&19")
End If
If Sheet10.Cells(i, 14).Value = "33" Then
l = l + 1
Sheet18.Range("a&l:s&l") = Sheet10.Range("a&i:s&i")
End If
Next
End With
这是我的一段代码,
我的想法是另一张工作表名等于sheet10中某一列中某个单元格的时候 ,把这一行所有的内容都输入到另一张工作表,主要是一行有几十列,我用上面的方面行不通
请问问题出在哪里?改怎么改啊
本帖最后由 sunjing-zxl 于 2012-1-9 21:23 编辑
- Sub 拆分工作表()
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- Dim sht As Worksheet, bm As String
- Dim arr1, arr2, arr3
- Dim d As New Dictionary
- Dim i As Long, j As Long, n As Long, m As Long
- Dim s As Long
- arr1 = Range("A1:N" & [B65536].End(xlUp).Row)
- arr2 = Application.Index(arr1, , 7)
- For i = 2 To UBound(arr2)
- d(arr2(i, 1)) = ""
- Next i
- For i = 0 To d.Count - 1
- m = 0
- bm = d.Keys(i)
- ReDim arr2(1 To UBound(arr1), 1 To 14)
- For n = 1 To UBound(arr1)
- If arr1(n, 7) = bm Or n = 1 Then
- m = m + 1
- arr3 = Application.Index(Application.Transpose(arr1), , n)
- For j = 1 To 14
- arr2(m, j) = arr3(j, 1)
- Next j
- End If
- Next n
- s = 6
- For Each sht In Worksheets
- If sht.Name = bm Then
- s = MsgBox("工作表已存在,是否删除", 4)
- If s = 6 Then
- Sheets(bm).Delete
- End If
- Exit For
- End If
- Next
- If s = 6 Then
- Set sht = Sheets.Add
- sht.Name = bm
- sht.Columns("B:B").NumberFormatLocal = "@"
- Sheets(bm).Range("A1").Resize(UBound(arr1), 14) = arr2 'Application.Transpose(Application.Transpose(arr2))
- Else
- s = Sheets(bm).Range("B65536").End(xlUp).Row + 1
- Sheets(bm).Range("A" & s).Resize(UBound(arr1), 14) = arr2
- Sheets(bm).Rows(s & ":" & s).Delete
- End If
- Next i
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码附件:
拆分-sunjing.rar
(1.53 MB, 下载次数: 13)
|
|