Easy Date Vba

**LewisLonsdale** · 01-20-2015, 08:33 AM

Hello, I just wish to get the date to be date - 1 (which works), however I want it to save only dates during the week, here is my code. I tried changing the date to workdate - 1 and it gave me a date in 1899.

Sub Save_Files()

Dim xDate As Date
xDate = WorkDate - 1

Application.ScreenUpdating = False
On Error GoTo err_handle

'Format(xDate, "dd-mm-yyyy")

'Saving MORE Reports
'NBCGFDELTAINSTRUMENT
Workbooks.Open Filename:="y:\Risk\Risk Report\MORE\NBCGFDELTAINSTRUMENT.xls"
ActiveWorkbook.SaveAs Filename:= _
"y:\Risk\Risk Report\MORE\NBCGFDELTAINSTRUMENT " & Format(xDate, "dd-mm-yyyy") & ".xls"
ActiveWorkbook.Close

**davesexcel** · 01-20-2015, 08:35 AM

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**LewisLonsdale** · 01-20-2015, 09:04 AM

Sorry this is the code


Sub Save_Files()

Dim xDate As Date
xDate = WorkDate - 1

Application.ScreenUpdating = False
On Error GoTo err_handle

Workbooks.Open Filename:="y:\Risk\Risk Report\MORE\NBCGFDELTAINSTRUMENT.xls"
ActiveWorkbook.SaveAs Filename:= _
"y:\Risk\Risk Report\MORE\NBCGFDELTAINSTRUMENT " & Format(xDate, "dd-mm-yyyy") & ".xls"