更新时间:2023-01-24 19:33:54
准备(仅需一次):
- 选择文件>选项。
- 在左侧导航窗格中选择"自定义功能区"。
- 在右侧的"主选项卡"列表中,勾选标记为"开发人员"的复选框。 li>
- 单击"确定"。
创建宏:
- 激活"开发人员"选项卡功能区。
- 单击"Visual Basic"。
- 选择"插入">模块。
- 将以下代码复制到出现的代码模块中:
Sub SplitData()
Const lngNameCol =第二列中的2'名称(B)
Const lngFirstRow = 2'数据从第2行开始
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngTargetRow As Long
Application.ScreenUpdating = False
设置wshSource = ActiveSheet
lngLastRow = wshSource.Cells(wshSource.Rows.Count, lngNameCol)。End(xlUp).Row
For lngRow = lngFirstRow to lngLastRow
if wshSource.Cells(lngRow,lngNameCol).Value<> wshSource.Cells(lngRow - 1,lngNameCol).Value然后
设置wshTarget = Worksheets.Add(After:= Worksheets(Worksheets.Count))
wshTarget.Name = wshSource.Cells(lngRow,lngNameCol) .Value
wshSource.Rows(lngFirstRow - 1)。复制目的地:= wshTarget.Cells(1,1)
lngTargetRow = 2
结束如果
wshSource.Rows(lngRow) .Copy Destination:= wshTarget.Cells(lngTargetRow,1)
lngTargetRow = lngTargetRow + 1
Next lngRow
Application.ScreenUpdating = True
End Sub
- 根据需要更改代码开头的两个常量 - lngNameCol是NAME列的编号,lngFirstRow是第一行,数据*不包括标题行
- 切换回Excel。
- 仍然在功能区的"开发人员"选项卡上,单击"宏"。
- 选择"SplitData",然后单击"运行"。
Hi,
How to split data into multiple worksheets based on column in excel 2013.
i have searched it in google. they told you can do it in Enterprise Tab in excel, but i have not there in enterprise tab in my excel.
this is my input.
this is my output
split the data based on name column in sheet1.
Thanks
Abdul Khadir
Preparation (needed only once):
- Select File > Options.
- Select 'Customize Ribbon' in the navigation pane on the left.
- In the list of 'Main Tabs' on the right, tick the check box labeled Developer.
- Click OK.
Create a macro:
- Activate the Developer tab of the ribbon.
- Click 'Visual Basic'.
- Select Insert > Module.
- Copy the following code into the code module that appears:
Sub SplitData() Const lngNameCol = 2 ' names in second column (B) Const lngFirstRow = 2 ' data start in row 2 Dim wshSource As Worksheet Dim wshTarget As Worksheet Dim lngRow As Long Dim lngLastRow As Long Dim lngTargetRow As Long Application.ScreenUpdating = False Set wshSource = ActiveSheet lngLastRow = wshSource.Cells(wshSource.Rows.Count, lngNameCol).End(xlUp).Row For lngRow = lngFirstRow To lngLastRow If wshSource.Cells(lngRow, lngNameCol).Value <> wshSource.Cells(lngRow - 1, lngNameCol).Value Then Set wshTarget = Worksheets.Add(After:=Worksheets(Worksheets.Count)) wshTarget.Name = wshSource.Cells(lngRow, lngNameCol).Value wshSource.Rows(lngFirstRow - 1).Copy Destination:=wshTarget.Cells(1, 1) lngTargetRow = 2 End If wshSource.Rows(lngRow).Copy Destination:=wshTarget.Cells(lngTargetRow, 1) lngTargetRow = lngTargetRow + 1 Next lngRow Application.ScreenUpdating = True End Sub
- Change the two constants at the beginning of the code as needed - lngNameCol is the number of the NAME column and lngFirstRow is the first row with data *excluding the header row)
- Switch back to Excel.
- Still on the Developer tab of the ribbon, click Macros.
- Select SplitData, then click Run.