1.開啟目標excel,按alt + f11鍵開啟VBE視窗.選擇插入->模組貼上下面程式碼到編輯器中

Sub 保留表頭拆分資料為若干新工作簿()

Dim arr, d As Object, k, t, i&, lc%, rng As Range, c%

c = Application.InputBox("請輸入拆分列號", , 4, , , , , 1)

If c = 0 Then Exit Sub

Application.ScreenUpdating = False

Application.DisplayAlerts = False

arr = [a1].CurrentRegion

lc = UBound(arr, 2)

Set rng = [a1].Resize(, lc)

Set d = CreateObject("scripting.dictionary")

For i = 2 To UBound(arr)

If Not d.Exists(arr(i, c)) Then

Set d(arr(i, c)) = Cells(i, 1).Resize(1, lc)

Else

Set d(arr(i, c)) = Union(d(arr(i, c)), Cells(i, 1).Resize(1, lc))

End If

Next

k = d.Keys

t = d.Items

For i = 0 To d.Count - 1

With Workbooks.Add(xlWBATWorksheet)

rng.Copy .Sheets(1).[a1]

t(i).Copy .Sheets(1).[a2]

.SaveAs Filename:=ThisWorkbook.Path & "\" & k(i) & ".xls"

.Close

End With

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

MsgBox "完畢"