VBA save files with date in the name

E.g.

Option Explicit

Public Sub SplitEachWorksheet()

  Const DATE_FORMAT As String = "yyyymmdd" ' ISO 8601, no delimiter, lexical sortable.
  Const NAME_DATE_DELIMITER As String = "-"

  On Local Error GoTo LocalError

  Dim CurrentPath As String
  Dim NewFilename As String
  Dim CurrentSheet As Excel.Worksheet

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  CurrentPath = Application.ActiveWorkbook.Path & "\"
  For Each CurrentSheet In ThisWorkbook.Sheets
    CurrentSheet.Copy
    NewFilename = CurrentSheet.Name & NAME_DATE_DELIMITER & Format(Now(), DATE_FORMAT) & ".xlsx"
    Application.ActiveWorkbook.SaveAs Filename:=CurrentPath & NewFilename
    Application.ActiveWorkbook.Close False
  Next CurrentSheet

  Set CurrentSheet = Nothing
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  Exit Sub

LocalError:
  Set CurrentSheet = Nothing
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  MsgBox Err.Number & ": " & Err.Description

End Sub

Select allOpen in new window

ste5an - I get an error on the Format - not sure why?

This exact thing happened on other code I used. I don't know why it won't run that Format

It works as it is on my system ;)

1) The workbook where the code is hosted, must be saved before running the macro.
2) Format() issues normally indicate an Offices installation issue with different Office versions. Check the references in the VBA IDE under Tools/References.
3) Test it in the immediate window as:

?Format(Now(), "yyyymmdd")

Select allOpen in new window

What error do you get exactly?

 Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & Date() & ".xlsx"

Select allOpen in new window

If the slashes give error
replace(date(),"/","-")

Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & replace(Date(),"/","-") & ".xlsx"

Select allOpen in new window

Caveat: Using Date() like this leads to an implicit format call using the users current short date settings. Working with international users, this leads to different naming schemes. Also are the common short date formats not lexical sortable.

John - getting error that wrong number of arguments. Error on both - so sorry, can't figure this out!

Error on both - so sorry, can't figure this out!

What about posting the exact error message, maybe as screenshot?

btw, as you use the worksheet name for file name generation, do you use any fancy sheet names?

ste5an - the sheet names are not fancy at all.

Euro5, you need to include the full set of code you are running, and also a screen capture showing thee act error and line location.

Hi,
Try

Sub SplitEachWorksheet()
Dim FPath As String
Dim strFilename As String




FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & Cstr(Date()) & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

You didn't say what the date should look like or where it should be placed in the name but try this.

Sub SplitEachWorksheet()
Dim FPath As String
Dim strFilename As String
Dim strNow As String
Dim ws As Worksheet

strNow = Format(Now, "yyyymmdd")
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & "_" & strNow & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

I get an error on line 7 Format
I think I must not have the library I need?

What is the error? You could also try changing line 7 to
strNow = Format(Now(), "yyyymmdd")

or

strNow = Format(Date, "yyyymmdd")

And in all three of the options you could prepend the assignment with VBA. e.g. strNow = VBA.Format(Now(), "yyyymmdd")

Did you check points 2) and 3)?

Maybe a solution would be to share the Workbook