Private Sub CommandButton1_Click()
'
Dim ClosedWorkbookSheetName As String
Dim FileExtention As String
Dim NewWorkbookName As String
Dim ClosedSourceWorkbook As Workbook
'
ClosedWorkbookSheetName = "report" ' <--- Set this to the sheet name in the closed workbook
FileExtention = ".xlsx" ' <--- Set this to the file extention of workbook to be created
NewWorkbookName = "ThisWeek" ' <--- Set this to the name of the workbook to be created
'
Workbooks.Add.SaveAs Filename:=NewWorkbookName & FileExtention ' Create/Name/save New Workbook
'
ThisWorkbook.Activate ' Reactivate this workbook
'
Application.ScreenUpdating = False ' Turn ScreenUpdating off
'
Set ClosedSourceWorkbook = Workbooks.Open(TextBox2.Value) ' Get value from TextBox to open the source file
'
' Copy sheet to beginning of the newly created workbook
ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
ClosedSourceWorkbook.Close SaveChanges:=False ' Close the source workbook
'
ThisWorkbook.Activate ' Reactivate this workbook
'
Set ClosedSourceWorkbook = Workbooks.Open(TextBox1.Value) ' Get value from TextBox to open the source file
'
' Copy sheet to beginning of the newly created workbook
ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
ClosedSourceWorkbook.Close SaveChanges:=False ' Close the source workbook
'
Application.DisplayAlerts = False ' Turn off DisplayAlerts to avoid sheet deletion warning
Sheets("Sheet1").Delete ' Delete Sheet1 from the newly created workbook
Application.DisplayAlerts = True ' Turn DisplayAlerts back on
'
ActiveWorkbook.Close True ' Save newly created file and close
'
Application.ScreenUpdating = True ' Turn ScreenUpdating back on
End
End Sub