Excel 2003 template that saves the filename based on a cell value
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    ThisWorkbook.SaveAs [B2] & "_" & Format(Date, "m-d-yy")
    Cancel = True
    Application.EnableEvents = True
End Sub

ThisWorkbook.SaveAs ThisWorkbook.Worksheets("Sheet Name Here").Cells(2,2) ...