1.拆分单元格赋值Sub 拆分填充()Dim x As RangeFor Each x In edRange.CellsIf x.MergeCells Thenx.Selectx.UnMergeSelection.Value = x.ValueEnd IfNext xEnd Sub2.E xcel 宏按列拆分多个excelSub Macro1()Dim wb As Workbook, arr, rng As Range, d As Object, k, t, sh As Worksheet, i& Set rng = Range("A1:f1")Application.ScreenUpdating = FalseApplication.DisplayAlerts = Falsearr = Range("a1:a" & Range("b" & Cells.Rows.Count).End(xlUp).Row)Set d = CreateObject("scripting.dictionary")For i = 2 To UBound(arr)If Not d.Exists(arr(i, 1)) ThenSet d(arr(i, 1)) = Cells(i, 1).Resize(1, 13)ElseSet d(arr(i, 1)) = Union(d(arr(i, 1)), Cells(i, 1).Resize(1, 13)) End IfNextk = d.Keyst = d.ItemsFor i = 0 To d.Count - 1Set wb = Workbooks.Add(xlWBATWorksheet)With wb.Sheets(1)rng.Copy .[A1]t(i).Copy .[A2]End Withwb.SaveAs Filename:=ThisWorkbook.Path & "\" & k(i) & ".xlsx"wb.CloseNextApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueMsgBox "完毕"End Sub3.E xcel 宏按列拆分多个sheet在一个工作表中是许多的公司订单记录，如何将它按公司名分拆成一个个工作表，用VBA 实现相当便捷。

以下是演试：原始工作簿：运行VBA代码后的工作簿：代码如下：1.需要先把数据按照分拆的那一列字段排序2.如果你想应用在你的表格中，只需将所有resize(1,3)中的3修改，改成你的表格的列数。

如果你总表有8列就改成resize(1,8)即可3.如果你想根据表格的第一列拆分，需要把Sheet1.Cells(i, 2) <> Sheet1.Cells(i - 1, 2)和 = Sheet1.Cells(i, 2)的2换成1Sub s()Application.ScreenUpdating = FalseDim sh As Worksheet, i As IntegerFor i = 2 To Sheet1.[a65536].End(3).RowIf Sheet1.Cells(i, 2) <> Sheet1.Cells(i - 1, 2) ThenWorksheets.Add after:=Worksheets(Sheets.Count)Set sh = ActiveSheet = Sheet1.Cells(i, 2)sh.Range("a1").Resize(1, 3).Value = Sheet1.Range("a1").Resize(1, 3).Valuesh.Range("a65536").End(3).Offset(1, 0).Resize(1, 3).Value = Sheet1.Cells(i, 1).Resize(1, 3).ValueElsesh.Range("a65536").End(3).Offset(1, 0).Resize(1, 3).Value = Sheet1.Cells(i, 1).Resize(1, 3).ValueEnd IfNext iApplication.ScreenUpdating = TrueEnd Sub4.Excel 宏多工作表合并Function LastRow(sh As Worksheet)On Error Resume NextLastRow = sh.Cells.Find(what:="*", _After:=sh.Range("A1"), _Lookat:=xlPart, _LookIn:=xlFormulas, _SearchOrder:=xlByRows, _SearchDirection:=xlPrevious, _MatchCase:=False).Row On Error GoTo 0End FunctionSub s()Dim sh As WorksheetDim DestSh As WorksheetDim Last As LongDim shLast As LongDim CopyRng As RangeDim StartRow As LongApplication.ScreenUpdating = FalseApplication.EnableEvents = False'新建一个“汇总”工作表Application.DisplayAlerts = FalseOn Error Resume NextActiveWorkbook.Worksheets("汇总").DeleteOn Error GoTo 0Application.DisplayAlerts = TrueSet DestSh = ActiveWorkbook.Worksheets.Add = "汇总"'开始复制的行号，忽略表头，无表头请设置成1StartRow = 2For Each sh In ActiveWorkbook.WorksheetsIf <> ThenLast = LastRow(DestSh)shLast = LastRow(sh)If shLast > 0 And shLast >= StartRow ThenSet CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))If Last + CopyRng.Rows.Count > DestSh.Rows.Count ThenMsgBox "内容太多放不下啦！"GoTo ExitSubEnd IfCopyRng.CopyWith DestSh.Cells(Last + 1, "A").PasteSpecial xlPasteValues.PasteSpecial xlPasteFormatsApplication.CutCopyMode = FalseEnd WithEnd IfEnd IfNextExitSub:Application.Goto DestSh.Cells(1)DestSh.Columns.AutoFitApplication.ScreenUpdating = TrueApplication.EnableEvents = TrueEnd Sub5.多个sheet拆成多个excelSub Macro1()Dim sht As WorksheetApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseFor Each sht In Sheetssht.CopyActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & & ".xlsx"ActiveWorkbook.CloseNextApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueEnd Sub或者Private Sub 分拆工作表()Dim sht As WorksheetDim MyBook As WorkbookSet MyBook = ActiveWorkbookFor Each sht In MyBook.Sheetssht.CopyActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & , FileFormat:=xlNormal '将工作簿另存为EXCEL默认格式ActiveWorkbook.CloseNextMsgBox "文件已经被分拆完毕!"End Sub6.利用txt提取文件夹中的所有文件名称1、在那个文件夹内新建一个.TXT文件（如wenjian.txt），用记事本单开输入dir> 1.txt 保存退出将刚才的.TXT（wenjian.txt）更名为.bat文件（wenjian.bat）双击wenjian.bat 文件运行一次，在文件夹内多出一个1.txt文件打开1.txt文件，将其中的内容粘贴到Excel中，数据——分列处理就可以得到你要的文件名列表了！7.一列拆成两列Excel电子表格的功能非常强大，无论是拆分还是合并单元格都可以轻松完成。

有时候我们编辑数据的时候将“名称”和“价格”全部放到了一个单元格中了，有什么方法可以快速将这些数据拆分开呢？下面Word联盟以具体实例来为大家详细介绍操作方法。

Excel表格中的数据拆分案例说明：水果名称与水果价格全部在一个单元格中，只是用“空格”分隔开。

我们将这些以空格分隔开的数据分别拆分到两个单元格中。

①首先，我们在Excel表格中选中需要拆分的列；②然后，单击菜单栏的“数据”，在下拉列表中选择“分列”命令；③此时，需要3个步骤来完成数据在表格中的拆分，“文本分列向导- 3 步骤之1”，我们只需选择默认的“分割符号”再单击下面的“下一步”按钮；④然后，继续在“文本分列向导- 3 步骤之2”下面的“分隔符号”中勾选“Tab 键”、“空格”和“连续分隔符号视为单个处理”。