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

by 
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

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. Run same excel macro on multiple excel files
  6. how to detect whether VBA excel found something?
  7. VBA : save a file with UTF-8 without BOM
  8. Excel tab sheet names vs. Visual Basic sheet names
  9. Excel VBA looping through multiple worksheets
  10. When one should use Set [e.g for SpecialCells return value]?
  11. Excel VBA How to detect if something was pasted in a Worksheet
  12. Getting error Procedure too large in VBA Macros (Excel)
  13. How to add data validation to a cell using VBA
  14. How to do late binding in VBA?
  15. How to break long string to multiple lines
  16. How to copy a chart from Excel to PowerPoint?
  17. Iterate over slicer via VBA and select a single item each time
  18. vba paste not working
  19. mac excel vba loop : from list & then export as pdf
  20. Can Advanced Filter criteria be in the VBA rather than a range?
  21. How can I open this VBA workbook faster?
  22. In Excel VBA, what is the way to check if the web page is fully loaded?
  23. Excel Useform: How to hide application but have icon in the taskbar
  24. Reading Web Pages using Excel VBA
  25. Excel VBA: auto click and open file from website
  26. How to call function from another specific workbook in VBA?
  27. VBA inheritance, analog of super
  28. Open Google Chrome from VBA/Excel
  29. Excel VBA Run-time error 1004 when inserting or value formula into cell
  30. How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

Leave a Comment