How do I name a spreadsheet automatically, by referencing two cells?

Question

I've just finished writing a spiffy macro for automatically generating reports. It works well, but I need it to automatically name the spreadsheet according to the data in two cells.

Essentially, this macro creates a new spreadsheet, copies the information over to it, and creates the relevant pivot-tables which are required monthly.

As part of this I've created a dashboard for generating the report with instructions and a date range the report is to relate to. It currently creates the spreadsheet "NEW REPORT". Is there a way of creating the new spreadsheet and naming it something along the lines of "Report 01.01.15 to 01.02.15" automatically?

I've got the date range as two separate cells, and I'm aware I'll have to make sure the date range is one that will use allowed characters (I.E. 01.01.15 rather than 01/01/15) - am I right in saying there's a way of telling the user they've put the dates in with the incorrect separators?

Answer 1

Option Explicit 
Sub SaveAs() 

    Dim FileName    As String 
    Dim FilePath    As String
    Dim FName       As String 

    FilePath = "C:\Temp" 
    FileName = Sheets("Sheet1").Range("A1").Text
    FName = Sheets("Sheet1").Range("B1").Text
    ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & FName

End Sub 

---

To save it on today's date

Dim sSave       As String
sSave = "Reports " & Format(Date, "dd-mm-yyyy")

---

Or tomorrow Date

"Reports" & Format(Date + 1, "dd-mm-yyyy")

---

For File Format See Examples

ThisWorkbook.SaveAs Filename:=FilePath, fileformat:=52 

These are the main file formats in Excel 2007-2013

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

*Or maybe you want to save the one worksheet workbook to csv, txt or prn.*

".csv": FileFormatNum = 6
".txt": FileFormatNum = -4158
".prn": FileFormatNum = 36

---

To Save only one Sheet as new Workbook then you need to copy the sheet before saving it

Option Explicit
Sub SaveAs()
    Dim Sht As Worksheet
    Dim FileName As String
    Dim FilePath As String

    FilePath = "C:\Temp"
    FileName = Sheets("Sheet1").Range("A1").Text

    Set Sht = ActiveWorkbook.Sheets("Sheet1")

    Sht.Copy

    ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName

End Sub

---

To Save Multiple sheets as new Workbook then use Sheets(Array("Sheet1", "Sheet2")).Copy

Option Explicit
Sub SaveAs()
    Dim Sht As Worksheet
    Dim Book As Workbook
    Dim FileName As String
    Dim FilePath As String

    FilePath = "C:\Temp"
    FileName = Sheets("Sheet1").Range("A1").Text

    Set Book = ActiveWorkbook

    With Book
        .Sheets(Array("Sheet1", "Sheet2")).Copy
    End With

    ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName

End Sub

---