Euro5
asked on
VBA save files with date in the name
This works perfectly, but how do I add the date to the file name?
Thank you!!!
Thank you!!!
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 & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
ASKER
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
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:
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")
What error do you get exactly?
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & Date() & ".xlsx"
If the slashes give errorreplace(date(),"/","-")
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & replace(Date(),"/","-") & ".xlsx"
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.
ASKER
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?
ASKER
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
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
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
ASKER
I get an error on line 7 Format
I think I must not have the library I need?
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")
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window