如何在Excel中将一个大表拆分为多个小表？

Sub Splitdatabycol() by Extendoffice Dim lr As Long Dim ws As Worksheet Dim vcol, i As Integer Dim icol As Long Dim myarr As Variant Dim title As String Dim titlerow As Integer Dim xTRg As Range Dim xVRg As Range Dim xWSTRg As Worksheet On Error Resume Next Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8) If TypeName(xTRg) = "Nothing" Then Exit Sub Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8) If TypeName(xVRg) = "Nothing" Then Exit Sub vcol = xVRg.Column Set ws = xTRg.Worksheet lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row title = xTRg.AddressLocal titlerow = xTRg.Cells(1).Row icol = ws.Columns.Count ws.Cells(1, icol) = "Unique" Application.DisplayAlerts = False If Not Evaluate("=ISREF(xTRgWs_Sheet!A1)") Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet" Else Sheets("xTRgWs_Sheet").Delete Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet" End If Set xWSTRg = Sheets("xTRgWs_Sheet") xTRg.Copy xWSTRg.Paste Destination:=xWSTRg.Range("A1") ws.Activate For i = (titlerow + xTRg.Rows.Count) To lr On Error Resume Next If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol) End If Next myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants)) ws.Columns(icol).Clear For i = 2 To UBound(myarr) ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & "" If Not Evaluate("=ISREF(" & myarr(i) & "!A1)") Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & "" Else Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count) End If xWSTRg.Range(title).Copy Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1") ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count)) Sheets(myarr(i) & "").Columns.AutoFit Next xWSTRg.Delete ws.AutoFilterMode = False ws.Activate Application.DisplayAlerts = True End Sub

Sub Splitdatabyrows() Updated by Extendoffice Dim WorkRng As Range Dim xRow As Range Dim SplitRow As Integer Dim xWs As Worksheet Dim xTRg As Range Dim xNTRg As Range Dim xIER On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8) If TypeName(xTRg) = "Nothing" Then Exit Sub Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8) If TypeName(WorkRng) = "Nothing" Then Exit Sub SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1) If SplitRow = 0 Then Exit Sub Set xWs = WorkRng.Parent Set xRow = WorkRng.Rows(1) xIER = WorkRng.Rows.Count xIER = WorkRng.Row + xIER - 1 Application.ScreenUpdating = False For i = 1 To WorkRng.Rows.Count Step SplitRow resizeCount = SplitRow If (xIER - xRow.Row + 1) < SplitRow Then resizeCount = (xIER - xRow.Row + 1) End If xRow.Resize(resizeCount).Copy Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count)) If xIER > (xRow.Row + SplitRow - 1) Then xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1) ElseIf xIER = xRow.Row Then xWs.Name = xRow.Row Else xWs.Name = xRow.Row & " - " & xIER End If Application.ActiveSheet.Range("A1").PasteSpecial Set xNTRg = Application.ActiveSheet.Range("A1") xTRg.Copy xNTRg.Insert Set xRow = xRow.Offset(SplitRow) Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

提示：申请这个 拆分数据 功能，首先，您应该下载 Kutools for Excel，然后快速轻松地应用该功能。

拆分工作簿以在Excel中分离Excel文件 您可能需要将一个大型工作簿拆分为单独的Excel文件，然后将工作簿的每个工作表另存为单独的Excel文件。 例如，您可以将工作簿拆分为多个单独的Excel文件，然后将每个文件交付给不同的人来处理。 这样，您可以使某些人处理特定数据，并确保您的数据安全。 本文将介绍根据每个工作表将大型工作簿拆分为单独的Excel文件的方法。

在Excel中将全名拆分为名字和姓氏 假设您有一个名称列表，如下面的单个列中所示，第一个屏幕快照，则需要将全名拆分为名字列，中间名称列和姓氏列，如下面的屏幕快照所示。 这里有一些棘手的方法可以帮助您解决此问题。

在Excel中将长列拆分为多列 如果您在Excel中有很长的列数据，则在查看它们时会很麻烦。 但是现在，如果您可以在Excel中将如此长的列表拆分为多个列，则将使查看变得很方便。

在Excel中将单词或数字拆分为单独的单元格 如果您在工作表中有数字或单词的列表，现在需要按照下面的屏幕快照将单元格内容拆分为不同单元格上的字母，那么如何在Excel中处理此工作？

在Excel中将长列表拆分成相等的组 如果需要将一长串数据分成多个相等的组，如下面的屏幕截图所示，那么如何在Excel中快速轻松地处理此任务？