To get today's date in VBA use the function Date on its own [Excel equivalent of =Today() ]
Wrap the format function around it to format it as you want.
I always go for YYYYMMDD because the files are then sorted in date sequence based on file name which is my preference. (Use DDMMYYYY if you prefer)
Amendments to the macro:
- declare the variable dateStr as string
- give dateStr a value(=today's date) and format it,
- include strDate as part of the file name
If you want a gap between date and text in the name, use something like:
Sub NewWBS()
Dim PathA As String, PathB As String, PathC As String, PathD As String, PathE As String
Dim dateStr As String
Dim wbNew As Workbook
Dim ws As Worksheet
PathA = "c:\myFolde\mysubfolderforModelA"
'etc for the others
dateStr = Format(Date, "YYYYMMDD")
For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wbNew = ActiveWorkbook
If ws.Name = "ModelA" Then wbNew.SaveAs PathA & "\" & dateStr & ws.Name
If ws.Name = "ModelB" Then wbNew.SaveAs PathB & "\" & dateStr & ws.Name
If ws.Name = "ModelC" Then wbNew.SaveAs PathC & "\" & dateStr & ws.Name
If ws.Name = "ModelD" Then wbNew.SaveAs PathD & "\" & dateStr & ws.Name
If ws.Name = "ModelE" Then wbNew.SaveAs PathE & "\" & dateStr & ws.Name
wbNew.Close
Next ws
End Sub
Bookmarks