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.



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

"JLGWhiz" wrote:

My typo. The second value should also have a period

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


"NFL" wrote in message
...
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" wrote:

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

After the above line, add this:

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



"NFL" wrote in message
...
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


.



.