SaveAs Multiple Worksheets into Multiple Workbooks

Sub CopySheetsToNewWorkbooks()
  Dim wSheet As Worksheet
  Dim sBook1 As String, sBook2 As String
  
  sBook1 = vbNullString
  sBook2 = vbNullString
  
  On Error Resume Next
  For Each wSheet In ThisWorkbook.Sheets
    Select Case wSheet.Name
      Case "100" To "199"
        If sBook1 = vbNullString Then
          'copy the "data" sheet to the new workbook
          ThisWorkbook.Sheets("Data").Copy
          'and grab the name of the new workbook
          sBook1 = ActiveWorkbook.Name
        End If
        With Workbooks(sBook1)
          wSheet.Copy After:=.Sheets(.Worksheets.Count)
        End With
        
      Case "200" To "299"
        If sBook2 = vbNullString Then
          'copy the "data" sheet to the new workbook
          ThisWorkbook.Sheets("Data").Copy
          'and grab the name of the new workbook
          sBook2 = ActiveWorkbook.Name
        End If
        With Workbooks(sBook2)
          wSheet.Copy After:=.Sheets(.Worksheets.Count)
        End With
    End Select
    Err.Clear
  Next
  On Error GoTo 0
End Sub

Sub CopySheetsToNewWorkbooks()
  Dim wSheet As Worksheet
  Dim sBook(1 To 2) As String
  Dim i As Integer
  
  For i = 1 To 2
    sBook(i) = vbNullString
  Next
  
  On Error Resume Next
  
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
  End With
  
  If ActiveWorkbook.Name <> ThisWorkbook.Name Then ThisWorkbook.Activate
  
  For Each wSheet In ThisWorkbook.Sheets
    Select Case wSheet.Name
      Case "100" To "199"
        If sBook(1) = vbNullString Then
          'copy the "necessary" sheets to the new workbook
          CopyBaseSheets sBook(1)
        End If
        With Workbooks(sBook(1))
          wSheet.Copy After:=.Sheets(.Worksheets.Count)
        End With
        
      Case "200" To "299"
        If sBook(2) = vbNullString Then
          'copy the "necessary" sheets to the new workbook
          CopyBaseSheets sBook(2)
        End If
        With Workbooks(sBook(2))
          wSheet.Copy After:=.Sheets(.Worksheets.Count)
        End With
    End Select
    Err.Clear
  Next
  
  'now we can try and hide the "Data" sheet in the new workbooks
  'this will work as long as there are other visible sheets in the workbook
  'we will also try and save the new workbook
  For i = 1 To 2
    If sBook(i) <> vbNullString Then
      With Workbooks(sBook(i))
        .Sheets("Data").Visible = xlSheetHidden
        'use the counter value (i) to set the beginning of the name
        'if the file already exists, it will be overwritten as Application.DisplayAlerts is False
        'the file will be saved to the current default folder
        'if the file needs to be saved in another folder, then the filename should be fully qualified
        'eg, "C:\MyDocs\Excel\" & i & "00.xls"
        .SaveAs Filename:=i & "00.xls", _
          FileFormat:=xlNormal, Password:=vbNullString, WriteResPassword:=vbNullString, _
          ReadOnlyRecommended:=False, CreateBackup:=False
      End With
    End If
  Next
  
  On Error GoTo 0
  
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
  End With
End Sub


Private Sub CopyBaseSheets(wBookName As String)
  'this will copy all the 'base' sheets to a new workbook and set the name of the new workbook
  wBookName = vbNullString
  With ThisWorkbook
    CopyHiddenSheet "Data", wBookName
    CopyHiddenSheet "Input", wBookName
    CopyHiddenSheet "Assumptions", wBookName
    CopyHiddenSheet "AE Flu Tracings", wBookName
  End With
End Sub


Private Sub CopyHiddenSheet(ByVal sShtName As String, Optional wBookName As String = vbNullString)
  Dim fHidden As Boolean
  
  On Error GoTo QuickExit
  With ThisWorkbook
    'unhide the source sheet if necessary and copy it
    fHidden = .Sheets(sShtName).Visible
    .Sheets(sShtName).Visible = xlSheetVisible
    'if we have no workbook name yet, we are starting a new workbook
    If wBookName = vbNullString Then
      .Sheets(sShtName).Copy
      wBookName = ActiveWorkbook.Name
    Else
      'otherwise we are adding sheets to the newly created workbook
      .Sheets(sShtName).Copy After:=Workbooks(wBookName).Sheets(Workbooks(wBookName).Worksheets.Count)
    End If
    'now hide the sheet again if necessary
    .Sheets(sShtName).Visible = fHidden
  End With
  
  'we can hide everything except the first sheet ("Data") in the new workbook
  With Workbooks(wBookName)
    If .Worksheets.Count > 1 Then
      .Sheets(.Worksheets.Count).Visible = xlSheetHidden
    End If
  End With


QuickExit:
End Sub