Save File in Desired Location & Name

[Kam]

Hi,

Need ur help to design macro which can save the file in desired location &
save with desired name. I do run a Macro on daily basis, which pulls data
from system based on Yesterday dates. Can you please write a macro.

File Name Should be : Tariff IHE - "Yesterday Date"
File Location : C:My Documents/Reports/

Best Regards,
Kam

 

[Gary''s Student]

Change the names to protect the innocent:

Sub SaveProperly()
Dim wb As Workbook, dt As String
Set wb = ActiveWorkbook
st = Format(Date - 1, "mm_dd_yyyy")
fname = "C:\Documents and Settings\jravensw\Desktop\Tariff IHE - " & st &
".xls"
MsgBox st
wb.SaveAs Filename:=fname
End Sub

 

[JLatham]

Mine is a bit different from Gary''s: it uses Environ$("USERPROFILE") to
return the drive and folder and your name portion of the path. This would
cause it to save the file in the "My Documents\Reports\" folder for whoever
logged into the computer. I also re-arranged the format of the date so that
it appears as YYYY-MM-DD with month and day both being 2-digit values. This
will cause the files to be presented to you in a file browser in order by
date properly, if that's of any importance/use to you:

Sub SaveMyFile()

ActiveWorkbook.SaveAs _
Environ$("USERPROFILE") & "\My Documents\Reports\Tariff IHE - " _
& Format(Now() - 1, "yyyy-mm-dd")

End Sub