Error when saving new workbook name

Question

I have been trying for most of the day to get this to work, but I can't figure out what is stopping the save process in this code:

Sheets("DailyReview").Select
Sheets("DailyReview").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
File_Name = "\\Il-svr\company\department\team\Schedules\Daily_Review_Email\city\" & Range("A110").Value & "\" & Range("D110").Value & "\DailyReview_" & Range("C110").Value & ".xlsx"
ActiveWorkbook.SaveAs Filename:=File_Name

The last line of code gives the error: "Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed. But a file actually does get renamed, just not "saved" to the path location. The path location is completely 100% correct and works fine when I save the macro workbook using the same path location. I am also using Excel 2010 and trying to save it in 2010 format. I have also tried setting the FileFormat to xlWorkbookDefault (51) with no success. I do have permission to save to this path and works fine if I save the new book manually. The ranges are just date values. For instance, the file would save as DailyReview_122914.xlsx if I used this code for today's date. I hope this is enough information. I appreciate any help.

Does the code use any references? I used to get this when a link to a reference was broken. You can check in the VBA editor by going to Tools -> References and then looking if it says its not finding something. There are plenty of other things that cause this error though. — Max
It doesn't seem like it's using any references. Only the 4 main VBAProject selections are check marked. Like I said, if I save the main template file (macro workbook) using basically the same code, I don't get any errors and the file saves with the name and location it should. I should mention this is the entire code. Nothing precedes it or is after it (except Sub"" and End). — Jirtizy
What values are in ranges A110, D110, & C110? Please validate that File_Name is a valid path and file name. — RubberDuck

Jirtizy Jirtizy · Accepted Answer · 2014-12-30T16:12:01

Thank you @RubberDuck. The values were blank for cell A110 which I think was throwing it off. I rewrote the code and discarded the value of cell A110 and it works fine now. It really is the simplest things some time. Thank you!

Error when saving new workbook name

1 Answers