Export Specific Area of Data into new Workbook

Please see post#3 for a better version...

Hi stdar2 - Try this:

Sub Save_Sheets()
  Dim w, n
  w = Split("Sheet1,Sheet2,Sheet3,Sheet4", ",")
  n = Split("name1,name2,name3,name4", ",")
  For i = 0 To UBound(w)
      ThisWorkbook.Worksheets(w(i)).Copy
      ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\Users\YOUR_USER_NAME_HERE\Desktop\" & n(i) & ".xlsx"
  Next
End Sub

Obviously, you'll have to adjust all the names, as well as the destination.

After further testing: this example overwrites old versions of each workbook automatically (if such exist).

Sub Save_Sheets2()
  Dim w, n
  w = Split("Sheet1,Sheet2,Sheet3,Sheet4", ",")                   'Worksheet names
  n = Split("bookname1,bookname2,bookname3,bookname4", ",")       'New workbook names
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  For i = 0 To UBound(w)
      ThisWorkbook.Worksheets(w(i)).Copy
      With ActiveWorkbook
        .ConflictResolution = xlLocalSessionChanges
        .Close SaveChanges:=True, Filename:="C:\Users\YOUR_USER_NAME_HERE\Desktop\" & n(i) & ".xlsx"
      End With
  Next
  
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True

End Sub