Loop Through Open Workbooks
In the example below, a new workbook is created by copying a worksheet. Once created, the logic loops through any open workbooks to find the newly created workbook (which can be identified with the starting four letters "Book"), then saves it with a different name and closes it.
Program Code
Option Explicit
Sub CreateAndSaveWorkbook()
Dim fname As String
Dim wbook As Workbook
Dim wkbWorkbook As Workbook
Dim wkbWorkbookToSave As Workbook
Set wkbWorkbookToSave = Nothing
Set wbook = ActiveWorkbook
fname = "C:\TempArea\Data"
On Error Resume Next
wbook.Sheets("MyData").Copy
' *************************************************
' Look For The Newly Created Workbook with Data
' *************************************************
For Each wkbWorkbook In Workbooks
If Left(wkbWorkbook.Name, 4) = "Book" Then
Set wkbWorkbookToSave = wkbWorkbook
Exit For
End If
Next wkbWorkbook
' *************************************************
' Save The New Workbook
' *************************************************
If Not (wkbWorkbookToSave Is Nothing) Then
Application.DisplayAlerts = False
wkbWorkbookToSave.SaveAs Filename:=fname, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
End If
' *************************************************
' Close All But The XLSM - Changes already saved
' *************************************************
Application.DisplayAlerts = False
wkbWorkbook.Close Savechanges:=False
wkbWorkbookToSave.Close Savechanges:=False
Application.DisplayAlerts = True
On Error GoTo 0
End Sub