如何将excel中的一个工作表按照某列拆分成多个sheet工作表呢?接下来就利用VBA工具来解决这个问题。 拆分之前的工作表:
拆分之后的工作表:
操作步骤具体如下:
第一步:打开需要拆分的表格文件:点击“开发工具—>查看代码”命令,如图所示:
第二步:右击“WPS表格对象—>插入—>模块”命令,如何所示:
第三步:在新建的“模块1”中粘贴VBA代码:如图所示:
具体代码如下:
Option ExplicitOption Base 1Sub 按指定列分组拆分数据()Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseDim self As WorksheetSet self = ActiveSheetDim nLastRowNum As LongDim nLastColumnNum As LongDim i As Long' 删除其他的sheetFor i = Sheets.Count To 1 Step -1If Sheets(i).Name <> self.Name ThenSheets(i).DeleteEnd IfNext iApplication.DisplayAlerts = TrueApplication.ScreenUpdating = True'获取全部数据范围nLastRowNum = Cells(Rows.Count, 1).End(xlUp).RownLastColumnNum = Cells(nLastRowNum, Columns.Count).End(xlToLeft).Column'获取标题Dim titleRange As RangeSet titleRange = Application.InputBox(prompt:="请选择标题区域:将要当做标题行的每一个单元格", Type:=8)' 有效数据开始行Dim nRowValidData As LongnRowValidData = titleRange.Row + titleRange.Rows.Count' 获取拆分列的信息,只需要列号Dim splitColumnRange As RangeSet splitColumnRange = Application.InputBox(prompt:="请选择拆分的列:选择任何一个该列的单元格即可", Type:=8)Dim columnNumToSplit As LongcolumnNumToSplit = splitColumnRange.Column' 需要拆分的值字典Dim splitValueDict As Object' 辅助字典用来保证顺序Dim splitValueDictReverse As ObjectDim indexArray() As LongSet splitValueDict = CreateObject("Scripting.Dictionary")Set splitValueDictReverse = CreateObject("Scripting.Dictionary")Dim cellValue As StringDim ws As WorksheetFor i = nRowValidData To nLastRowNum Step 1cellValue = Cells(i, columnNumToSplit).Text'1. 创建新的sheet;'2. 拷贝标题信息到新的sheetIf Not splitValueDict.Exists(cellValue) ThensplitValueDict(cellValue) = isplitValueDictReverse(i) = cellValueSet ws = Sheets.Add(After:=Worksheets(Worksheets.Count))ws.Name = cellValueself.ActivatetitleRange.Copy _ws.Range(ws.Cells(titleRange.Row, titleRange.Column), ws.Cells(nRowValidData - 1, titleRange.Column))End If' 拷贝其他内容Range(Cells(i, 1), Cells(i, nLastColumnNum)).Copy _GetLastPasteRangeBySheetName(cellValue, nLastColumnNum)Next iEnd SubPublic Function GetLastPasteRangeBySheetName(ByRef SheetName As String, columnNum As Long) As VariantDim wks As WorksheetDim nLastRowNum As LongSet wks = ActiveWorkbook.Worksheets(SheetName)nLastRowNum = wks.Cells(wks.Rows.Count, 1).End(xlUp).RowSet GetLastPasteRangeBySheetName = wks.Range(wks.Cells(nLastRowNum + 1, 1), wks.Cells(nLastRowNum + 1, columnNum))End Function
第四步:点击“开发工具—>VB宏”命令,单击运行,如图所示:
第五步:选择拆分后在工作表(sheet)中需要显示的标题,点击确定,如图所示:
第六步:选择以某列进行拆分的条件(注意:本次是以“班级”为条件进行拆分),点击确定。如图所示:
这样就大功告成了!
如果觉得《将excel中的一个工作表按照某列拆分成多个sheet工作表》对你有帮助,请点赞、收藏,并留下你的观点哦!