macro problem

[d0wnt0wn]

hi... I made this macro to save a file based on the name in a cell but i get

a runtime 1004 error method 'savea' of object_workbook failed if there is already a file of that name in the folder and i select no or cancel instead of yes.... any way to fix that???

also... is it possible to save just the active worksheet to a location and not the whole workbook?

 

[martinee]

Try something like this:

Sub Whaterver()
On Error GoTo userC

'your code here
'
'
'
Exit Sub

userC:
MsgBox "User Cancelled, File Not Saved!"
Exit Sub

End Sub

HTH.

 

[scifibum]

If you want to save just one worksheet, you need your macro to copy the sheet into a new workbook and save that.

Here's an example. Keep in mind that the "Copy" method of a worksheet automatically creates a new workbook with the copied sheet when you don't specify the Before or After arguments, and that new workbook is the ActiveWorkbook until another one is activated or it's closed:

Sub SaveActiveSheet()
    
    ActiveSheet.Copy
    
    ActiveWorkbook.SaveAs Filename:="C:\test.xls"
    
    ActiveWorkbook.Close

End Sub

 

[erik.van.geit]

Hi, d0wnt0wn,
second question

is it possible to save just the active worksheet to a location and not the whole workbook?

ActiveSheet.Copy
this makes a new file of the sheet which you can then save ...
Activeworkbook.Save or Saveas ...
kind regards,
Erik

 

[d0wnt0wn]

thanks for the replies all i will try them and let you know how i did

 

[d0wnt0wn]

ok heres how it went.... martinee.... the code worked very well except.... is there a way that i can have the user be given the option to name it something else rather than just close the workbook......

Scifibum....... worked like a charm thanks