怎么拆分表格，Excel拆分表格的6种方法(详细介绍)

5、选择-将筛选结果复制到其他位置

6、列表区域选择-源数据区域

7、条件区域选择-条件区域

8、复制到-新创建的标题区

9、最后点击-确定

3、表头函数输入1

4、关键字所在列：【A】月份

5、点击-确定

（添加储君老师：chujun2012，免费领取方方格子插件）

Sub 工作表拆分()

Dim d As Object, sht As Worksheet, arr, brr, r, kr, i&, j&, k&, x&

Dim Rng As Range, Rg As Range, tRow&, tCol&, aCol&, pd&

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Set d = CreateObject("scripting.dictionary")

Set Rg = Application.InputBox("请输入拆分条件所在的列", Title:="提示", Type:=8)

tCol = Rg.Column

tRow = Val(Application.InputBox("请输入总表标题的行数"))

If tRow = 0 Then MsgBox "未输入标题行行数，程序退出！": Exit Sub

Set Rng = ActiveSheet.UsedRange

arr = Rng

tCol = tCol - Rng.Column + 1

aCol = UBound(arr, 2)

For i = tRow + 1 To UBound(arr)

If Not d.exists(arr(i, tCol)) Then

d(arr(i, tCol)) = i

Else

d(arr(i, tCol)) = d(arr(i, tCol)) & "," & i

End If

Next

For Each sht In Worksheets

If d.exists(sht.Name) Then sht.Delete

Next

kr = d.keys

For i = 0 To UBound(kr)

If kr(i) <> "" Then

r = Split(d(kr(i)), ",")

ReDim brr(1 To UBound(r) + 1, 1 To aCol)

k = 0

For x = 0 To UBound(r)

k = k + 1

For j = 1 To aCol

brr(k, j) = arr(r(x), j)

Next

Next

With Worksheets.Add(, Sheets(Sheets.Count))

.Name = kr(i)

.[a1].Resize(tRow, aCol) = arr

.[a1].Offset(tRow, 0).Resize(k, aCol) = brr

Rng.Copy

.[a1].PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.[a1].Select

End With

End If

Next

Sheets(1).Activate

Set d = Nothing

Erase arr: Erase brr

MsgBox "工作表拆分完成！"

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

3、筛选-1月

4、点击-表1-复制

本文地址：IT知识频道 https://www.eeeoo.cn/itzhishi/886067_2.html，嗨游网一个专业手游免费下载攻略知识分享平台，本站部分内容来自网络分享，不对内容负责，如有涉及到您的权益，请联系我们删除，谢谢！