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