CODEWITHSUNDEEP
vbaexcel-2010
Jirtizy
Jirtizy

Reputation: 3

Error when saving new workbook name

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.

Upvotes: 0

Views: 174

Answers (1)

Jirtizy
Jirtizy

Reputation: 3

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!

Upvotes: 0

Related Questions