Excel – VBA – How To create a New Workbook

Place the code below into the standard module:

Sub AddSaveAsNewWorkbook()
	Dim Wk As Workbook
	Set Wk = Workbooks.Add
	Application.DisplayAlerts = False
	Wk.SaveAs Filename:="C:/MyData/SalesData.xls"
	Application.DisplayAlerts = True
End Sub

HeelpBook(c) Article's integration

Instead, if you want to create a common workbook inserting only one sheet inside it you can use the following code:

Sub AddSaveAsNewWorkbook()
	Dim Wk As Workbook
	Set Wk = Workbooks.Add(1)
	Application.DisplayAlerts = False
	Wk.SaveAs Filename:="C:/MyData/SalesData.xls"
	Application.DisplayAlerts = True
End Sub

Finally, if you want to close the new workbook after saved it and save changes (it's repetitive), you can use the following code:

Sub AddSaveAsNewWorkbook()
	Dim Wk As Workbook
	Set Wk = Workbooks.Add(1)
	Application.DisplayAlerts = False
	Wk.SaveAs Filename:="C:/MyData/SalesData.xls"
        Wk.Close SaveChanges:=True
	Application.DisplayAlerts = True
End Sub