value of M6 = Now()

This can be a problem item. You are using Now() as a file name. One tenth
of a second after you assign the value of Now() to a variable, the value of
Now() is different. The variable still holds the original value of the time
that it was assigned, but you will never find that value in another file
name that was previously assigned, so looking for it is an exercise in
futility. To shorten all that up to an understandiable explanation, Now()
returns a value that is asnapshot of the current date and time such as:
5/27/2010 10:59:23 as a numeric data type. So the value of Now() is
constantly changing as the clock ticks. In the case of your code, the value
will change each time the worksheet is reactivated. That would be on
Workbook.Open and selecting another sheet then returning to that sheet. So
you will never be able to find a previously assigned filename the way the
code is written, because the Now() value that is used as a file name is
unique. You need to re-think this project and see if there is something
more reliable you can use for a file name. the =Today() function only
returns the date as a numeric data type, You might be able to use the
Format function:
FileName1 = Format(Range("M6").Value, "d/m/yyyy") & ".xls"
That would give you a date as a string data type which you could then search
for as a file name But as you have it now, FileName1 does not equate to a
valid file name.















"NFL" wrote in message
...
Here's the summary of what is going on. The code below does create the
file
to the designated path. Here are the values.... F6 = J:\Data\Export and
the
value of M6 = Now()

Problem: When the file is exported, the formulas are being carried over
to
the new file. I do not want the formulas and references passed on. What
happens is after the file is created I get a Run-time error '9': Subscript
out of range error message. When I press the Debug button it takes me to
this line....

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

Hope that makes sense and thank you for your reply. I've been trying
several methods to make this work and I'm not getting anywhere.

"JLGWhiz" wrote:

I did not test this, but it should work if the values in F6 and M6 are
valid
names and properly configured. If it does not work, then post back with
any
error messages received and what line of code is highlighted when the
error
occurs.


Private Sub CommandButton6_Click()

Dim message As String, FolderName1 As String, FileName1 As String
Set MyComputer = CreateObject("Scripting.FileSystemObject")
FolderName1 = Range("F6").Value
FileName1 = Range("M6").Value
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.")
ElseIf 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
Sheet18.Visible = xlSheetVisible
Sheets("Export").Copy
ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal
With Workbooks(FileName1).Sheets(1).Cells
.Value = .Value
End With
ActiveWorkbook.Close
End If
End If

End Sub





"NFL" wrote in message
...
I posted a message earlier and been trying to figure out why this code
doesn't work right. Thank you for all your help.

Private Sub CommandButton6_Click()

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

Sheet18.Visible = xlSheetVisible
Sheets("Export").Copy
ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal

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

ActiveWindow.Close
End If
End If

End Sub


.