'Save As' Macro Debug Error
 

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

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 to
save an existing worksheet to a new workbook. However, when a
duplication file name exists it prompts me to save as and if I click
'No' I get a debug error message. I'd still like the 'save as' message
to come up but is there a way I can get eliminate the debug error
message if 'No' is clicked? This is so it just works normally when
saving as a different file name or clicking no when an invoice is
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 With
--------------------


Any help would be greatly appreciated.

Kind regards,

Brown Teddy Bear


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

polar;430581 Wrote:
--------------------------------------------------------------------------------

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 to
save an existing worksheet to a new workbook. However, when a
duplication file name exists it prompts me to save as and if I click
'No' I get a debug error message. I'd still like the 'save as' message
to come up but is there a way I can get eliminate the debug error
message if 'No' is clicked? This is so it just works normally when
saving as a different file name or clicking no when an invoice is
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 With
--------------------


Any help would be greatly appreciated.

Kind regards,

Brown Teddy BearWhat is the error code that you get?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

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


--
Regards,

OssieMac


"polar" wrote:


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

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 to
save an existing worksheet to a new workbook. However, when a
duplication file name exists it prompts me to save as and if I click
'No' I get a debug error message. I'd still like the 'save as' message
to come up but is there a way I can get eliminate the debug error
message if 'No' is clicked? This is so it just works normally when
saving as a different file name or clicking no when an invoice is
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 With
--------------------


Any help would be greatly appreciated.

Kind regards,

Brown Teddy Bear


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

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 very
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Polar


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

Glad we could be of help!
polar;430625 Wrote:
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 very
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Polar


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

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" wrote:


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 very
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Polar


--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605

'Save As' Macro Debug Error
 

Hi Barb,

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

Kind regards,

Polar

Barb Reinhardt;430685 Wrote:
If I just want to close a workbook, instead of using this


Code:
--------------------

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

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

I use this

Code:
--------------------
ActiveWorkbook.Close SaveChanges:=False
--------------------


HTH,
Barb Reinhardt

"polar" wrote:


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
very
well.

Thank you again Simon and OssieMac for your help.

Kind regards,

Polar


--
polar

------------------------------------------------------------------------
polar's Profile: 'The Code Cage Forums - View Profile: polar'
(http://www.thecodecage.com/forumz/member.php?userid=564)
View this thread: ''Save As' Macro Debug Error - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=119605)




--
polar
------------------------------------------------------------------------
polar's Profile: http://www.thecodecage.com/forumz/member.php?userid=564
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119605