VBA - Copy and paste values before saving

[leolbi]

Hi all!

I'm using a macro to copy data from a sheet and save it as a new .csv file.
I'm currently using the below which I got from searching online, which does exactly what I was expecting:

Sub Export()
Dim MyPath As String
Dim MyFileName As String


MyFileName = "SW_" & Sheets("Creator").Range("C3").Value & "_CampaignBuild_" & Format(Date, "ddmmyyyy")


If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"


Sheets("Google Upload").Copy


With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
    MyPath = .SelectedItems(1) & "\"
End With


NextCode:


With ActiveWorkbook
    
    .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close False
End With
End Sub

However, for some reason, the CSV file loads with a few #REF errors from some named ranges (but not all named ranges!) even though the original spreadsheet presents these values fine.
So, I'd like to add a line to paste the cells as values in the code above to see if that helps.

Can anyone help me achieve this?

 

[leolbi]

For more information, the cells causing the errors use INDIRECT - '=INDIRECT("Phrase"&ROW($A1)))'

 

[tonyyy]

You might try...

Sub Export()
Dim MyPath As String
Dim MyFileName As String

MyFileName = "SW_" & Sheets("Creator").Range("C3").Value & "_CampaignBuild_" & Format(Date, "ddmmyyyy")
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Google Upload").Copy
[COLOR=#ff0000]With ActiveSheet.UsedRange
    .Value = .Value
End With[/COLOR]
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
    MyPath = .SelectedItems(1) & "\"
End With
NextCode:
With ActiveWorkbook
    .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close False
End With
End Sub

Cheers,

tonyyy

 

[leolbi]

Thanks tonyyy.

Unfortunately, the exported CSVs still have #REF errors and don't work!

Any other ideas?