macro problem

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Try something like this:
Code:
Sub Whaterver()
On Error GoTo userC

'your code here
'
'
'
Exit Sub

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

End Sub

HTH.
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
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:

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

End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Oct 28, 2002
Messages
771
thanks for the replies all i will try them and let you know how i did
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top