VBA code to rename and save file doesn't always save the files with formulas.


New Member
Nov 27, 2015
Hello i have the following code:
Sub XX()
Dim Path1 As String
Dim filename As String
Dim MyOldName As String
Dim thisWb As Workbook
Dim CO As String
Dim newname As String
filename = ("XX")
Set thisWb = ActiveWorkbook
myfirstname = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) 'ActiveWorkbook.Name & xlTypePDF '".pdf" '"ZNA " & Filename2 & " " & Filename & ".xls"
MyOldName = ActiveWorkbook.FULLNAME

Application.DisplayAlerts = False

CO = Range("K7").Value

If CO = "JX" Then
Path1 = "C:\Users\Public\Dropbox (JX Purchasing)\NEW PEND OR NOCAL\PO TO CONFIRM\TODAYS TO SEND\"
End If
If CO = "JFC" Then
Path1 = "C:\Users\Public\Dropbox (JX Purchasing)\NEW PEND OR SOCAL\PO TO CONFIRM\TODAYS TO SEND\"
End If

ActiveWorkbook.SaveAs filename:=Path1 & filename & " " & myfirstname, FileFormat:=xlNormal
Kill MyOldName

ActiveWorkbook.Close SaveChanges:=True

End Sub

The code basically renames the file, saves the file to a diff location, deletes the old file.
The code works fine it seems - except for occasionally the files that are saved (they have a bunch of vlookup formulas in them linking to external files.) instead of saving the formulas , the vlookup value is saved in a cells but the formulas are no longer there - instead it has a =#VALUE! in the formula bar when i click on the cells... All the external links are gone as well.. I have never encountered this issue before, any help would be greatly appreciated. Using Office 365.

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Watch MrExcel Video

Forum statistics

Latest member