Thank you both so much for the help. I used the degugger and when the line
crashes the variable is defined correctly. Stepping into the code causes
failure at the same line and this is the error message I get:

Runtime error 1004

Microsoft Excel cannot access the file 'C:\Program Files\Common Files\
system\Mapi\1033.' There are several possible reasons:

The file name or path name does does not exist

The file you are trying to open is being used by another program
(Close the document in the other program and try again)

The name of the workbook you're trying to save is the same as the .....(the
rest is cut off in the window can't be seen)

I want the file to save in the users default location - I can't really set a
direct path because users computers are all set up differently and I don't
know what would work for sure on all of them.

Christy ;)


"Vacation's Over" wrote:

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With