This assumes that all sheets in the workbook currently contain a series in column A beginning in cell A1.
Sub cat()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
On Error GoTo Hdl:
If Not sh.Name Like "Series*" Then
sh.Range("A1", sh.Cells(Rows.Count, 1).End(xlUp)).Copy _
Sheets("Series" & Left(sh.Range("A2").Value, 1)).Cells(Rows.Count, 1).End(xlUp)(2)
End If
Hdl:
If Err.Number = 9 Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Series" & Left(sh.Range("A2").Value, 1)
sh.Range("A1", sh.Cells(Rows.Count, 1).End(xlUp)).Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2)
Err.Clear
Resume Next
ElseIf Err.Number > 0 Then
MsgBox Err.Number & ": " & Err.Description & " Occurred while copying sheet " & sh.Name
Err.Clear
Resume Next
End If
Next
End Sub
Bookmarks