如果您想录制宏来看看会发生什么,请按照下列步骤操作:
- 打开宏录制器
- 按名称对数据进行排序
- 从名字复制数据
- 将其粘贴到另一张纸上(如果需要另一张纸,请添加一张纸)
- 为工作表命名
- 重复下一个名称
我还编写了一些您可以用来入门的代码。为了使其工作,您需要将数据选项卡命名为“MasterList”。该代码按名称对 MasterList 上的行进行排序,然后对于列表中的每个唯一名称,创建一个新工作表并将适当的数据复制到新工作表,重复该过程,直到所有名称都复制到新工作表。
将此代码添加到模块并运行DispatchTimeSeriesToSheets
程序。
Sub DispatchTimeSeriesToSheets()
Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
' stop processing if we don't have any data
If LastRow < 2 Then Exit Sub
Application.ScreenUpdating = False
SortMasterList LastRow, ws
CopyDataToSheets LastRow, ws
ws.Select
Application.ScreenUpdating = True
End Sub
Sub SortMasterList(LastRow As Long, ws As Worksheet)
ws.Range("A2:C" & LastRow).Sort Key1:=ws.Range("A1"), Key2:=ws.Range("B1")
End Sub
Sub CopyDataToSheets(LastRow As Long, src As Worksheet)
Dim rng As Range
Dim cell As Range
Dim Series As String
Dim SeriesStart As Long
Dim SeriesLast As Long
Set rng = Range("A2:A" & LastRow)
SeriesStart = 2
Series = Range("A" & SeriesStart).Value
For Each cell In rng
If cell.Value <> Series Then
SeriesLast = cell.Row - 1
CopySeriesToNewSheet src, SeriesStart, SeriesLast, Series
Series = cell.Value
SeriesStart = cell.Row
End If
Next
' copy the last series
SeriesLast = LastRow
CopySeriesToNewSheet src, SeriesStart, SeriesLast, Series
End Sub
Sub CopySeriesToNewSheet(src As Worksheet, Start As Long, Last As Long, _
name As String)
Dim tgt As Worksheet
If (SheetExists(name)) Then
MsgBox "Sheet " & name & " already exists. " _
& "Please delete or move existing sheets before" _
& " copying data from the Master List.", vbCritical, _
"Time Series Parser"
End
End If
Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = name
Set tgt = Sheets(name)
' copy header row from src to tgt
tgt.Range("A1:C1").Value = src.Range("A1:C1").Value
' copy data from src to tgt
tgt.Range("A2:C" & Last - Start + 2).Value = _
src.Range("A" & Start & ":C" & Last).Value
End Sub
Function SheetExists(name As String) As Boolean
Dim ws As Worksheet
SheetExists = True
On Error Resume Next
Set ws = Sheets(name)
If ws Is Nothing Then
SheetExists = False
End If
End Function