CODE QUESTION

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
Hi can someone please tell me if i can use a statement like "if file exists go to userC" instead of the on error statement as shown here?

Code:
Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

userC:
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + "#2" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub


End Sub


also I have tried usig this piece here to name a file with limited success....

[c5].Value + "#2" & ".xls" so if c5 had "cat" in the cell it would read cat #2 but i am having problems with it also.... any better way to do this???
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
hehe excuse my ignorance taz.....I have only been trying to use VBA for about 2 weeks and although I am becomming familiar with recognizing some of the snipits of code I am still at a loss on how to impliment it.... I have been trying to get some help on this for some time now but noone seems to know or the answer is too complicated for any one to help me with it..... I just cant figure out a way to get what i want to happen so I am kind of settling for this for now untill i can figure the other out.... what I really want to happen....

user clicks on button to save a worksheet from a workbook based on an address in a cell.
if file does not exist in location... save file as cell value.
If there is a file with that name already.... ask user to overwrite or save file with a suggested name of cell value plus a (#2) desiganation or something similar.
code saves file as cell value or cell value + (#2) and closes. MSG box pops up and says file was saved.

thats what I am ultimately trying to acheive



as far as the code goes i know that my code does not do that but I am trying to fix one thing at a time since i have not been able to get help with this
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Or instead of .SaveAs, use .SaveCopyAs: Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.
 

Forum statistics

Threads
1,147,681
Messages
5,742,587
Members
423,739
Latest member
plkdti

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