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

MFK85

New Member
Joined
Nov 27, 2015
Messages
11
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

Threads
1,112,865
Messages
5,542,946
Members
410,578
Latest member
schleeepy
Top