vba query - how do you save a work book with the name of previous working day?

[Bradrose84]

hi,

Id like to save a work book using a name of indexpricingDDMMYY, but where the date is the previous business/working date. I'd ideally not like to reference a cell in the workbook, just use function like workday-1 or something? I am able to save with the current date-1, but this does not work for holidays...


Sub Macro5()
'
Columns("A:H").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H7:H28").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy;@"
Range("B7:B28").Select
Selection.NumberFormat = "m/d/yy;@"
Range("B2").Select
Selection.NumberFormat = "m/d/yy;@"
ActiveWorkbook.SaveAs Filename:="T:\Brad\Indexpricing" & Format(Date - 1, "DDMMYY") & ".csv", FileFormat:=xlCSV, CreateBackup:= _
False
Range("L16").Select
ActiveWorkbook.Save

End Sub

 

[CharlesChuckieCharles]

ActiveWorkbook.SaveAs Filename:="T:\Brad\Indexpricing" & Format(application.worksheetfunction.Workday(Date,-1), "DDMMYY") & ".csv", FileFormat:=xlCSV,