VBA Create a new workbook with a button click

Question

I'm trying to create a macro that creates a new workbook when pressing a button on the already existing workbook. I would like to save the newly created workbook in the same folder as the exiting workbook and give it a new name? Could somebody help me please?

Thank you very much in advance!

That's the code I have until now but it doesn't work as I want it (object not found and doesn't save it in the same folder):

Sub CreateNewWorkBook()

'Adding New Workbook
Workbooks.Add
'Saving the Workbook
 ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\Test.xls"
 ActiveWorkbook.Close savechanges:=False

End Sub

Unknown Unknown · Accepted Answer · 2017-11-15T13:02:31

Try the following code from:

https://msdn.microsoft.com/en-us/library/office/aa221273(v=office.11).aspx

Sub AddNew()
Set NewBook = Workbooks.Add
    With NewBook
        .Title = "All Sales" 'You can modify this value.
        .Subject = "Sales" 'You can modify this value.
        .SaveAs Filename:="Allsales.xls"
    End With
End Sub

To get the path of the file there is already a question about that:

How to get the path of current worksheet in VBA?

VBA Create a new workbook with a button click

1 Answers