Excel中是否可以根据单个列的内容将一个大文件拆分为一系列较小的文件?
例如:我有所有销售代表的销售数据文件。我需要向他们发送一个文件以进行更正并发送回去,但是我不想向他们每个人发送整个文件(因为我不想让他们更改彼此的数据)。该文件如下所示:
salesdata.xls
RepName Customer ContactEmail
Adam Cust1 [email protected]
Adam Cust2 [email protected]
Bob Cust3 [email protected]
etc...
为此,我需要:
salesdata_Adam.xls
RepName Customer ContactEmail
Adam Cust1 [email protected]
Adam Cust2 [email protected]
和salesdata_Bob.xls
Bob Cust3 [email protected]
Excel 2007内置了什么功能可以自动执行此操作,还是我应该打破VBA?
据我所知,没有一个宏可以分割数据并将其自动保存到一组文件中。VBA可能更容易。
更新我实现了我的建议。它遍历命名范围“ RepList”中定义的所有名称。命名范围是形式为= OFFSET(Names!$ A $ 2,0,0,COUNTA(Names!$ A:$ A)-1,1)的动态命名范围
模块如下。
Option Explicit
'Split sales data into separate columns baed on the names defined in
'a Sales Rep List on the 'Names' sheet.
Sub SplitSalesData()
Dim wb As Workbook
Dim p As Range
Application.ScreenUpdating = False
For Each p In Sheets("Names").Range("RepList")
Workbooks.Add
Set wb = ActiveWorkbook
ThisWorkbook.Activate
WritePersonToWorkbook wb, p.Value
wb.SaveAs ThisWorkbook.Path & "\salesdata_" & p.Value
wb.Close
Next p
Application.ScreenUpdating = True
Set wb = Nothing
End Sub
'Writes all the sales data rows belonging to a Person
'to the first sheet in the named SalesWB.
Sub WritePersonToWorkbook(ByVal SalesWB As Workbook, _
ByVal Person As String)
Dim rw As Range
Dim personRows As Range 'Stores all of the rows found
'containing Person in column 1
For Each rw In UsedRange.Rows
If Person = rw.Cells(1, 1) Then
If personRows Is Nothing Then
Set personRows = rw
Else
Set personRows = Union(personRows, rw)
End If
End If
Next rw
personRows.Copy SalesWB.Sheets(1).Cells(1, 1)
Ser personRows = Nothing
End Sub
该工作簿包含代码和命名范围。该代码是“销售数据”表的一部分。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句