Saving values of Excel cross-sheet formulas to csv

Question

Using other threads, I distilled a VBA script to save each sheet in an Excel workbook to a separate CSV file. One of my sheets contains formulas over data on another sheet. The cells with those formulas are saving as REF!s, and I haven't been able to figure out how to use .Value to fix the problem (I just want to export the values to CSV).

Here is the core of my script (I omitted some vars defns and other setup/cleanup code)

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(strApp+sFullPath)
oBook.Application.DisplayAlerts = FALSE
For Each objws In oBook.Sheets  
  objws.Copy  
  With objws.UsedRange
    .Value = .Value
  End With
  sheetName = Replace(objws.Name, " ", "")
  toName = strApp & sFilename & "-" & sheetName & ".csv"
  objws.SaveAs toName, 6
  oExcel.ActiveWorkbook.Close False  
Next 

Answer 1

You need to change it to values before you copy

With objws.UsedRange
   .Value = .Value
End With
objws.Copy

You can then in the end close the source workbook without saving.