Best Way To Combine Two Macros

[Captain_Conman]

Hello!

I currently have two macros that I use in order...

The first takes a large sheet of data ("100"), and split it into smaller sheets of 101 rows. (100 rows + 1 header). These sheets become titled "Raw 1", "Raw 2", "Raw 3" etc.

Sub DataToSheets()
    Dim src As Worksheet
    Dim lr As Long, lc As Long, x As Long
    Dim HeadArray As Variant, DataArray As Variant
    Dim i As Integer
    
Set src = ThisWorkbook.Sheets("100")
With src
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    HeadArray = .Range("A1").Resize(, lc).Value
    i = 1
    For x = 2 To lr Step 100
        DataArray = .Range("A" & x).Resize(100, lc).Value
        Sheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Cells(1, 1).Resize(, lc) = HeadArray
        ActiveSheet.Cells(2, 1).Resize(100, lc) = DataArray
        ActiveSheet.Cells.EntireColumn.AutoFit
        On Error GoTo Error_Handler
        ActiveSheet.Name = "Raw" & i
Error_Handler:
        i = i + 1
    Next x
End With
End Sub

The second takes those small sheets, duplicates them, and sorts the sheet into a better format. These sheets become "Sorted 1" "Sorted 2" "Sorted 3" etc.

Sub CreateSortedSheets()
    Dim wkSt As String
    Dim wkBk As Worksheet
    Dim i As Integer
    wkSt = ActiveSheet.Name
    i = 1
    For Each wkBk In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkBk.Activate
        Cells.Copy
        Sheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Paste
        Cells.Select
        Application.CutCopyMode = False
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "H2:H101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "A2:A101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A1:AD101")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        Columns("C:D").Select.NumberFormat = "#,##0.00_);(#,##0.00)"
        Range("A1").Select
        End With
        On Error GoTo Error_Handler
        ActiveSheet.Name = "Sorted " & i - 2
Error_Handler:
        i = i + 1
    Next wkBk
    Sheets(wkSt).Select
Application.DisplayAlerts = False
Sheets("Sorted -1").Delete
Sheets("Sorted 0").Delete
Sheets("100").Delete
Application.DisplayAlerts = True
End Sub

In the end, I have two sheets "Raw 1" and "Sorted 1" which contain the same data, but are sorted differently. This macros work great, but I would like to combine them into one procedure. I have tried multiple times on my own, but seem to keep screwing something up. I was hoping one of you VBA experts could help me combine and clean up these two macros.

Any help with this would be great! Thanks in advance.

 

[shg]

Untested:

Sub DataToSheets()
  Dim wksSrc        As Worksheet
  Dim iRow          As Long
  Dim iSht          As Long

  Set wksSrc = ThisWorkbook.Sheets("100")

  With wksSrc
    For iRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row Step 100
      Sheets.Add After:=Worksheets(Worksheets.Count)
      .Rows(1).Copy Range("A1")
      .Rows(iRow).Resize(100).Copy Range("A2")
      Columns.AutoFit
      
      iSht = iSht + 1
      ActiveSheet.Name = "Raw " & iSht
      ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = "Sorted " & iSht
      Cells.Sort Key1:=Range("H1"), Key2:=Range("A1"), Header:=xlYes
    Next iRow
  End With
End Sub