VBA to save file as values not always working

[woodsy74]

I have a macro button that opens up an excel template file, calculates it, saves it to a new location, copies all of the cells & pastes values, and then saves the file again. This is done for about 15 files. My issue is that sometimes when I run the macro button I will find one or two or three files that still have formulas in them and they are not values. It's not the same files that have this issue either. One time when I run it will be the 3rd and 5th files that still have formulas. The next time I run it the 8th file still has formulas. I've had other folks run this macro and so far when they've run it all of the files look good. Is there a setting or something that I need to adjust for this to work on all the files for me?

Here's the code:
Workbooks.Open Filename:="S:\FILE.xlsx", UpdateLinks:=3
ActiveWorkbook.Save

ActiveWorkbook.SaveAs Filename:="S:\FILE_NEW.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save


ActiveWorkbook.Close

 

[DanteAmor]

Try this:

Sub test()
  Dim w1 As Workbook, w2 As Workbook, sh As Worksheet
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set w1 = Workbooks.Open(Filename:="S:\FILE.xlsx", UpdateLinks:=3)
  w1.SaveAs Filename:="S:\FILE_NEW.xlsx", FileFormat:=xlOpenXMLWorkbook, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
  Set w2 = ActiveWorkbook
  Set sh = w2.Sheets(1)
  sh.Cells.Copy
  sh.Range("A1").PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
  w2.Save
  w2.Close False
End Sub

 

[woodsy74]

Thanks for the reply Dante. I updated the code with what you have listed but I get the same result. All of the files were saved out but the last two were still formulas and not values. I ran it again and this time the last two files were values but five files near the middle were all formulas.

 

[DanteAmor]

It's very weird.
Only the files have only one sheet?

You can put your complete code.

 

[MoshiM]

Thanks for the reply Dante. I updated the code with what you have listed but I get the same result. All of the files were saved out but the last two were still formulas and not values. I ran it again and this time the last two files were values but five files near the middle were all formulas.

Please read the commented code. Some of what you have originally is too ambiguous for me to decipher.

My understanding is that you want to open the template, convert the cells on that template to values and then save this new version of the template under a new name in a different location.

Why do you choose to open the template, save it , do what you want and then save again?

Just to be sure is "Activeworkbook" supposed to refer to the now open template?

Sub Values_Only()


Dim WB As Workbook, WR As Range, Values_Only As Variant


Set WB = Workbooks.Open(Filename:="S:\FILE.xlsx", UpdateLinks:=3)[COLOR=#0000ff]'open template and store reference to it in variable WB[/COLOR]


Set WR = WB.Worksheets(1).UsedRange'[COLOR=#0000ff]create reference to usedrange on first worksheet within template [/COLOR]

with WR


     Values_Only = .Value'[COLOR=#0000ff]place template values in an array[/COLOR]


    .Value = Values_Only'[COLOR=#0000ff]Overwrite cells on worksheet 1 of template[/COLOR]


End with

WB.SaveAs Filename:="S:\FILE_NEW.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False'[COLOR=#0000ff]save new version of  template with a different name & location[/COLOR]


End Sub

 

[DanteAmor]

Try this

Sub test()
  Dim w1 As Workbook, sh As Worksheet
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set w1 = Workbooks.Open(Filename:="S:\FILE.xlsx", UpdateLinks:=3)
  w1.SaveAs Filename:="S:\FILE_NEW.xlsx", FileFormat:=xlOpenXMLWorkbook, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
  Set sh = w1.Sheets(1)
  sh.Cells.Copy
  sh.Range("A1").PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
  w1.Save
  w1.Close False
End Sub

 

[woodsy74]

I see that you guys are changing the code. The existing code works. Other users run the macro and all of the files are saved and are values. So far it is only when I run the macro that we find a file or two saved with formulas still. I am wondering if there is maybe an excel setting or something that I need to enable/disable in order for it to work fully for me? It doesn't make sense to me that it works for others but not me. Or that it works on most files but not all. I would think they would all work or none would work since the code is the same for all files.

 

[MoshiM]

I see that you guys are changing the code. The existing code works. Other users run the macro and all of the files are saved and are values. So far it is only when I run the macro that we find a file or two saved with formulas still. I am wondering if there is maybe an excel setting or something that I need to enable/disable in order for it to work fully for me? It doesn't make sense to me that it works for others but not me. Or that it works on most files but not all. I would think they would all work or none would work since the code is the same for all files.

Does it do what you want it to though? You should consider commenting your code line by line if necessary.

Here is yours but commented:

Sub Values_Only()


Workbooks.Open Filename:="S:\FILE.xlsx", UpdateLinks:=3 '[COLOR=#0000ff]open template[/COLOR]
ActiveWorkbook.Save '[COLOR=#0000ff]save template[/COLOR]


ActiveWorkbook.SaveAs Filename:="S:\FILE_NEW.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False '[COLOR=#0000ff]save template again with different name and path[/COLOR]


Cells.Select                    '[COLOR=#0000ff]select all the cells on the current worksheet of template[/COLOR]
Selection.Copy                  '[COLOR=#0000ff]copy them[/COLOR]
Cells.Select                    '[COLOR=#0000ff]select cells again[/COLOR]
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False '[COLOR=#0000ff]paste back to worksheet with values only[/COLOR]
Range("A1").Select              '[COLOR=#0000ff]select first cell of worksheet[/COLOR]
Application.CutCopyMode = False '[COLOR=#0000ff]turn off copy paste[/COLOR]
ActiveWorkbook.Save             '[COLOR=#0000ff]save workbook[/COLOR]


ActiveWorkbook.Close            '[COLOR=#0000ff]close new template values only workbook[/COLOR]


End Sub

This one more closely resembles what you posted originally without certain redundancies

Sub Values_Only()

Dim WB As Workbook, WR As Range, Values_Only As Variant

Set WB = Workbooks.Open(Filename:="S:\FILE.xlsx", UpdateLinks:=3) '[COLOR=#0000ff]open template and store reference to it in variable WB[/COLOR]

With WB

    Set WR = .Worksheets(1).UsedRange '[COLOR=#0000ff]create reference to usedrange on first worksheet within template[/COLOR]
             .Save '[COLOR=#0000ff]save original template with updated links,calculations etc[/COLOR]
End With
[COLOR=#0000ff]'[/COLOR][COLOR=#ff0000]do you really need the above .save line?[/COLOR][COLOR=#0000ff][/COLOR]

With WR '[COLOR=#0000ff]with range object[/COLOR]

     Values_Only = .Value '[COLOR=#0000ff]place template values in an array[/COLOR]

    .Value = Values_Only '[COLOR=#0000ff]Overwrite cells on worksheet 1 of template[/COLOR]

End With

With WB

    .SaveAs Filename:="S:\FILE_NEW.xlsx", _
            FileFormat:=xlOpenXMLWorkbook, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False '[COLOR=#0000ff]save new version of  template(values only) with a different name & location[/COLOR]

    .Close '[COLOR=#0000ff]Close new template workbook (values only)[/COLOR]
    
End With

End Sub

 

[DanteAmor]

You tried the macro of post #7 .
What I am trying to establish is that the active book be the one that is copied and pasted as values.

The problem may be that the file is opened in the background and then it is not copying and pasting values ​​on the newly opened book, it does so on the book that contains the macro.

Nor did you respond to the request in post #4 .


We are just trying to help.

 

[woodsy74]

I was trying to figure out what you were looking for with the changes to the code. Thanks for the explanation.

The template files all have 3 sheets but the last 2 sheets are blank (I've asked if we can delete those 2 sheets). I just now put a simple =5*5 formula in A1 on the blank sheets. I also put this formula on the original file/sheet where we hit the macro button. When the macro was done it was still a formula on the original/file sheet. It was also still a formula on the 2 blank sheets that I updated in the templates.

Here is the full code for the TR Section of files (there are other sections using more files but the code is the same). I just ran this 5 times in a row: 2 times just West file still had formulas; 1 time West & South still had formulas; 1 time All files had formulas; and 1 time All files were good and had values.

Sub TR_Post()
 
Application.DisplayAlerts = False
 
   
     
 
'''----TR North---'''
    Workbooks.Open Filename:="S:\TR\TR North.xlsx", UpdateLinks:=3
    ActiveWorkbook.Save
   
    ActiveWorkbook.SaveAs Filename:="S:\TR_POSTED\TR North.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
       
'''----TR North---'''
 
'''----TR South---'''
    Workbooks.Open Filename:="S:\TR\TR South.xlsx", UpdateLinks:=3
    ActiveWorkbook.Save
   
    ActiveWorkbook.SaveAs Filename:="S:\TR_POSTED\TR South.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
       
'''----TR South---'''
 
 
'''----TR West---'''
    Workbooks.Open Filename:="S:\TR\TR West.xlsx", UpdateLinks:=3
    ActiveWorkbook.Save
   
    ActiveWorkbook.SaveAs Filename:="S:\TR_POSTED\TR West.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
       
'''----TR West---'''
   
   
 Application.ScreenUpdating = True
   
   
MsgBox "TR Post Finished"
  
    End Sub