Thread: VBA Save w/ date showing Wrong Date

hi,
try this

'
"C:\~VENDOR_VALIDATION\Vendor Validation Report" & Format$(Date, "dd-mmm-yyyy")
'

HI,


You should use the forum format for the code tags.
Here is your code which i added a correction too.
You can however record a macro to see how to format the SaveAs file.

 Format(Date, "MM - DD - YY")''' note the " " "

Sub SAVE_TEST()
'
' SAVE_TEST Macro
'
'
'


ChDir "C:\~VENDOR_VALIDATION"
ActiveWorkbook.SaveAs Filename:= _
"C:\~VENDOR_VALIDATION\Vendor Validation Report\" & Format(Now, "MM - DD - YY") & ".xls"

With ActiveSheet
Range("A1").Select
End With

End Sub

Hi,

It might help if we can see a copy of your workbook. All of the codes that were posted worked.

Hi,

The following is the code I used for my test.

' this is the saved as name Copy of North Vendor Validation Report 04 - 13 - 2011
ChDir "Z:\MacExcel\ExcelFiles"
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\MacExcel\ExcelFiles\Copy of North Vendor Validation Report " & Format(Now, "MM - DD - YYYY") & ".Xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

This is the result
Copy of North Vendor Validation Report 04 - 13 - 2011

Try the following code in your Personal code Module7

Sub SAVE_TO_C_DRIVE()
'
' SAVE_TO_C_DRIVE Macro
'
' Keyboard Shortcut: Ctrl+e
'

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Application.Run "PERSONAL.XLSB!FINAL_EDIT"
Application.Run "PERSONAL.XLSB!REMOVAL_BLANKET_REMOVE_BY_FILTER"
Application.Run "PERSONAL.XLSB!STATUS_REMOVE_BY_FILTER"
Application.Run "PERSONAL.XLSB!FREEZE"

    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select


'******************************************
    'WHEN VENDOR SHAEPOINT IS READY ACTIVATE
'ChDir "C:\~VENDOR_VALIDATION"
    'ActiveWorkbook.SaveAs Filename:= _
        '"Z:\North Vendor Validation Report.xls", FileFormat:=xlExcel8, _
       ' Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        'CreateBackup:=False
'******************************************

    ChDir "C:\~VENDOR_VALIDATION"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\~VENDOR_VALIDATION\North Vendor Validation Report " & Format(Now, "MM - DD - yyyy") & ".xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
Application.ScreenUpdating = True
        
        With ActiveSheet
        Range("A1").Select
        End With

Application.DisplayAlerts = True

End Sub

hi CBG,
it works for me too! just for a test, run with this code and see if it works for you with a test file.

Sub Button2_Click()
ActiveSheet.Copy

     'Saves filename as value of A1 plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("A1").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & "Sheet1 - " & Format$(Date, "dd-mmm-yyyy")
     ' Change directory to suit your PC, including USER NAME
    ChDir _
    "C:\users\john\Desktop"
    ActiveWorkbook.SaveAs Filename:=newFile
End Sub