[RESOLVED] Creating New Workbook

[lilmark]

I am using Excel 2007.

My automation is set and basically updates the Template with new data every month when they open the workbook (after they receive an email that the data has been updated). It also has a combobox/button which allows them to display previous months' data on the Template.

I am trying to add an additional button which will create a new workbook and copy the report from the Template over to the new workbook. This way they can just send the relevant data to other people and not the Template which has all the macros and such.

Creating and copying is the easy part. However, every time I have the macro create a new workbook the name changes Book1, Book2, Book3, Book4, etc. Is there any way to name the new workbooks being created beforehand so the macro refers to the correct workbook? Or to store the name of the new workbook created into a variable so I can just reference that variable in the macro and it always refers to the correct one in cases where multiple workbooks are created?

[Siddharth Rout]

This way of creating a new workbook involves assigning it to an object variable. In the code below, the Workbook object returned by the Add method is assigned to an object variable, XLBook. Next, then new name of XLBook is set. You can easily control the new workbook using the object variable.

Sub AddNew()
Set XLBook = Workbooks.Add
    With XLBook
        .SaveAs Filename:="MyFile.xls"
    End With
End Sub

Hope this helps...

[lilmark]

Awesome, this works greats. It automatically saves it to the desktop, right?

[Siddharth Rout]

No, I believe it saves to my documents. You will have to specify the path for example...

Sub AddNew()
    Set XLBook = Workbooks.Add
    With XLBook
        .SaveAs Filename:="C:\MyFFile.xls"
    End With
End Sub