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.
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.