vba拆分excel表格
时间: 2023-10-03 16:07:20 浏览: 57
可以使用VBA来拆分Excel表格,以下是一个简单的示例代码:
```vb
Sub SplitData()
'设置变量
Dim lastRow As Long
Dim fileName As String
Dim i As Long
Dim j As Long
Dim k As Long
'获取数据行数
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'循环遍历数据
For i = 2 To lastRow
'获取文件名
fileName = ActiveSheet.Cells(i, 1).Value
'创建新的工作簿
Workbooks.Add
'复制表头
For j = 1 To ActiveSheet.Columns.Count
ActiveSheet.Cells(1, j).Value = ActiveSheet.Cells(1, j).Value
Next j
'复制数据
k = 2
Do While ActiveSheet.Cells(i, 1).Value = fileName And i <= lastRow
For j = 1 To ActiveSheet.Columns.Count
ActiveSheet.Cells(k, j).Value = ActiveSheet.Cells(i, j).Value
Next j
k = k + 1
i = i + 1
Loop
'保存新的工作簿
ActiveWorkbook.SaveAs fileName & ".xlsx"
ActiveWorkbook.Close
Next i
End Sub
```
这个代码将数据按照第一列的不同值拆分成多个工作簿,并以第一列的值作为文件名保存。请注意,这个代码仅适用于数据较小的情况,对于大型数据集可能需要进行优化。