'Save As' Macro Debug Error

[polar]

--------------------------------------------------------------------------------

Hi Everybody,

Thank you for having me on this forum.

Thanks to the help of some Excel users, I was able to get some code t
save an existing worksheet to a new workbook. However, when
duplication file name exists it prompts me to save as and if I clic
'No' I get a debug error message. I'd still like the 'save as' messag
to come up but is there a way I can get eliminate the debug erro
message if 'No' is clicked? This is so it just works normally whe
saving as a different file name or clicking no when an invoice i
accidentally duplicated.

Please see below code:




Code
-------------------

With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"
ActiveWorkbook.SaveAs Filename:=invName
ActiveWorkbook.Close
End Wit
-------------------


Any help would be greatly appreciated.

Kind regards,

Brown Teddy Bea

 

[Simon Lloyd]

-------------------------------------------------------------------------------

Hi Everybody,

Thank you for having me on this forum.

Thanks to the help of some Excel users, I was able to get some code t
save an existing worksheet to a new workbook. However, when
duplication file name exists it prompts me to save as and if I clic
'No' I get a debug error message. I'd still like the 'save as' messag
to come up but is there a way I can get eliminate the debug erro
message if 'No' is clicked? This is so it just works normally whe
saving as a different file name or clicking no when an invoice i
accidentally duplicated

Please see below code:


Code
-------------------
With Sheets("Invoice2"
Dim invName As Strin
Sheets("Invoice2").Selec
invName = Range("L4").Value & ".xls
Sheets("Invoice2").Cop
ChDir "C:\Users\Justin\Documents\Razza Jam
ActiveWorkbook.SaveAs Filename:=invNam
ActiveWorkbook.Clos
End Wit -------------------

Any help would be greatly appreciated

Kind regards,

Brown Teddy BearWhat is the error code that you get

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[OssieMac]

Try the following. If not what you want then feel free to get back to me.

With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"

'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With

 

[polar]

Hi Simon and OssieMac,

Thank you for your responses.

The error message I was getting was:

"Application-time error 1004

Save as Object Failed"

This has now gone. The code you provided me with OssieMac works ver
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Pola

 

[Simon Lloyd]

Glad we could be of help!

Hi Simon and OssieMac,

Thank you for your responses.

The error message I was getting was:

"Application-time error 100

Save as Object Failed

This has now gone. The code you provided me with OssieMac works ver
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Pola

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Barb Reinhardt]

If I just want to close a workbook, instead of using this

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

I use this
ActiveWorkbook.Close SaveChanges:=False

HTH,
Barb Reinhardt

 

[polar]

Hi Barb,

Thank you for the tip. It's very helpful and much appreciated.

Kind regards,

Polar