|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
下面代码就是按末字符拆分工作表的:- Sub test1()
- Dim d As Object
- Dim r%, i%
- Dim arr
- Dim ws As Worksheet
- Set d = CreateObject("scripting.dictionary")
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- For Each ws In Worksheets
- If ws.Name <> "Sheet1" Then
- ws.Delete
- End If
- Next
-
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 4).End(xlUp).Row
- arr = .Range("d5:f" & r)
- End With
-
- For i = 1 To UBound(arr)
- xm = Right(Trim(arr(i, 2)), 1)
- d(xm) = d(xm) & "+" & i
- Next
-
- For Each aa In d.Keys
- Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
- With ws
- .Name = aa
- brr = Split(Mid(d(aa), 2), "+")
- ReDim crr(1 To UBound(brr) + 1, 1 To 3)
- For i = 0 To UBound(brr)
- For j = 1 To 3
- crr(i + 1, j) = arr(Val(brr(i)), j)
- Next
- Next
- .Range("a1").Resize(UBound(crr), UBound(crr, 2)) = crr
- End With
- Next
-
- End Sub
复制代码 |
|