|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub SplitSheet()
Dim lastRow As Long, lastColumn As Long, i As Long, j As Long
Dim dict As Object, ws As Worksheet
Set dict = CreateObject("scripting.dictionary")
lastRow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row
lastColumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
'插入新列
ActiveSheet.Columns(lastColumn + 1).Insert
'设置新列的值
For i = 2 To lastRow
ActiveSheet.Cells(i, lastColumn + 1).Value = WorksheetFunction.CountIf(Range("A$1:A" & i), Range("A" & i).Value)
Next i
'针对新列中每个值,加入字典
For i = 2 To lastRow
If Not dict.Exists(ActiveSheet.Cells(i, lastColumn + 1).Value) Then
dict.Add ActiveSheet.Cells(i, lastColumn + 1).Value, 1
End If
Next i
'按照新列中不同的值,将sheet1分成多个工作表
For Each Key In dict.keys
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.count))
ws.Name = Key
ws.Range("A1", ws.Cells(1, lastColumn)).Value = Sheet1.Range("A1", Sheet1.Cells(1, lastColumn)).Value
j = 2
For i = 2 To lastRow
If Sheet1.Cells(i, lastColumn + 1).Value = Key Then
ws.Range("A" & j, ws.Cells(j, lastColumn)).Value = Sheet1.Range("A" & i, Sheet1.Cells(i, lastColumn)).Value
j = j + 1
End If
Next i
Next Key
'删除新列
Sheet1.Cells(1, lastColumn + 1).EntireColumn.Delete
End Sub
|
|