How can I name a table automatically by referring to two cells?

Question

I just finished writing spiffy macro to automatically generate reports. It works well, but I need it to automatically determine the spreadsheet according to the data in two cells.

Essentially, this macro creates a new spreadsheet, copies it onto it, and creates the corresponding pivot tables that are required monthly.

As part of this, I created a panel for creating a report with instructions and a date range to which the report should relate. He is currently creating the " NEW REPORT " table . Is there a way to create a new spreadsheet and call it something like the line "Report 01/01/15 to 02/01/15" automatically?

I have a date range as two separate cells, and I know that I will need to make sure that the date range is the one that will use valid characters (IE 01.01.15, not 01/01/15) - Am I correct I say there is a way to tell the user that they entered dates with wrong 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 keep it for today

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 in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro 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 a new workbook, you need to copy the sheet before saving

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 a new workbook, 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

---