Export Only Values

[NFL]

Below is a code that works great and what it does is that it exports a
worksheet from the current workbook to a new workbook. The problem I'm
having is that I only want to copy the values and leave the formulas and
references behind. Any help would be appreciated.

Thank you for your help!

Dim message As String
Set MyComputer = CreateObject("Scripting.FileSystemObject")
FolderName1 = Range("F6")
FileName1 = Range("M6")
If MyComputer.FolderExists(FolderName1) = False Then
message = MsgBox("The directory: " & FolderName1 & ", does not
exist." & Chr(10) _
& "Enter a new directory path (cell F6).", 0, "Not today my friend.")
Else
If MyComputer.FileExists(FileName1) = True Then
message = MsgBox("The file name: " & FileName1 & ", already
exists." & Chr(10) _
& "A copy of this file has NOT been saved.", 0, "Not today my
friend.")
Else

'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel
Workbook (*.xls), *.xls")
Sheet18.Visible = xlSheetVisible
Sheets("Export").Copy
ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal
'ActiveWorkbook.SaveCopyAs FileName1
'Range("H1") = "I've Been Exported"
ActiveWindow.Close
End If
End If

 

[JLGWhiz]

ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal

After the above line, add this:

With Workbooks(FileName1).Sheets(1).Cells
.Value = Value
End With

 

[Paul W Smith]

After you have created the new copy workbook, copy all the cells and paste
then as values, then save again.

Workbooks([FileName1]).worksheets("Export").cells.copy
Workbooks([FileName1]).worksheets("Export").cells.PasteSpecial
Paste:=xlPasteValues

This will remove all formulas and leave just the values in each cell.

 

[NFL]

I entered the code as shown below and got a compile error msg "Variable Not
Defined". I pressed the Debug button and it highlighed the word Value right
after the equal sign. :-(

 

[JLGWhiz]

My typo. The second value should also have a period

With Workbooks(FileName1).Sheets(1).Cells
.Value = .Value

 

[NFL]

I tried your code and I got a runtime error 9: subscript out of range
message. I pressed the debug and it pointed to the first line of your code.
:-(

After you have created the new copy workbook, copy all the cells and paste
then as values, then save again.

Workbooks([FileName1]).worksheets("Export").cells.copy
Workbooks([FileName1]).worksheets("Export").cells.PasteSpecial
Paste:=xlPasteValues

This will remove all formulas and leave just the values in each cell.

Below is a code that works great and what it does is that it exports a
worksheet from the current workbook to a new workbook. The problem I'm
having is that I only want to copy the values and leave the formulas and
references behind. Any help would be appreciated.

Thank you for your help!

Dim message As String
Set MyComputer = CreateObject("Scripting.FileSystemObject")
FolderName1 = Range("F6")
FileName1 = Range("M6")
If MyComputer.FolderExists(FolderName1) = False Then
message = MsgBox("The directory: " & FolderName1 & ", does not
exist." & Chr(10) _
& "Enter a new directory path (cell F6).", 0, "Not today my
friend.")
Else
If MyComputer.FileExists(FileName1) = True Then
message = MsgBox("The file name: " & FileName1 & ", already
exists." & Chr(10) _
& "A copy of this file has NOT been saved.", 0, "Not today my
friend.")
Else

'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel
Workbook (*.xls), *.xls")
Sheet18.Visible = xlSheetVisible
Sheets("Export").Copy
ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal
'ActiveWorkbook.SaveCopyAs FileName1
'Range("H1") = "I've Been Exported"
ActiveWindow.Close
End If
End If

.

 

[NFL]

Well bummer .... now I got a different runtime error 9 message saying
subscript out of range.

 

[JLGWhiz]

Sorry for the delayed response.

From what I can determine from your code, the value in M6 is a file name.
The way it is used elsewhere in the code, FileName1 appears to be a variable
that holds that file name. If all that is true the Workbooks(FileName1)
should return the file object that you want to convert to values only in the
cells that display data. Based on those assumptions, the code worked when I
tested it. I do not know why you continue to get errors unless the
assumptions are incorrect, in which case you should get an error before you
reach that line.