edit this code please???

[d0wnt0wn]

i have a little snippet of code here that will save a file based on an address i type in c5 ..... is it possible to set it up so that if there is already an estimate of that address in there.... that an option pops up that will alow me to over write or save as address #2

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

userC:
MsgBox "There is a file with that name already, File Not Saved!"
Exit Sub

End Sub

 

[chiello]

.... that an option pops up that will alow me to over write or save as address #2

ActiveWorkbook.SaveAs Filename:="C:\Documents and

The SaveAs Method asks itself to overwrite if a file already exists. If you type No (to overwrite) the code generates an error, else the file will be overwritten and the code will flow to the end.

I suggest the following solution

Sub SaveActiveSheet()
Dim MyPrompt As String, MyTitle As String
Dim NewName As Variant

MyPrompt = "There is already a file with the name " & Range("C5") & ", File Not Saved!" _
& Chr(10) & Chr(10) & "Enter new Name"
MyTitle = "New File Name"
NewName = False
ActiveSheet.Copy
Label_SaveFile:
On Error GoTo userC
ActiveWorkbook.SaveAs Filename:=C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
While (NewName = False) Or (NewName = "")
NewName = Application.InputBox(prompt:=MyPrompt, Title:=MyTitle, Type:=2)
Wend
Range("C5") = NewName
GoTo Label_SaveFile
End Sub

Ciao

 

[chiello]

Sub SaveActiveSheet()
...
MyTitle = "New File Name"
NewName = False
ActiveSheet.Copy
...
userC:
While (NewName = False) Or (NewName = "")
...
End Sub

I think it is better if you place the statement NewName = False soon after userC Label, as follows:

Sub SaveActiveSheet()
...
MyTitle = "New File Name"
'Not Here
ActiveSheet.Copy
...
userC:
NewName = False
While (NewName = False) Or (NewName = "")
...
End Sub

 

[d0wnt0wn]

hi... I have all inds of erros when trying to use that code...... when i click on my button that i have made for the macro a box pops up and says there is already a file with that name do i want to replace..... no matter what i click the box will not go away...... and no file gets saved to my folder....also i would like the user to be given the choice wether or not to over write the original or replace it..... now if i click on the button and there is a file with that name in the folder... the box pops up and i enter a new name and i get a run time error that points here.

ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"

 

[tusharm]

Sub testIt()
    Dim FileName As Variant
    FileName = "g:\temp\" & Range("c5").Value & ".xls"
    FileName = Application.GetSaveAsFilename(FileName)
    If TypeName(FileName) = "String" Then
        ActiveWorkbook.SaveAs FileName
        End If
    End Sub

i have a little snippet of code here that will save a file based on an address i type in c5 ..... is it possible to set it up so that if there is already an estimate of that address in there.... that an option pops up that will alow me to over write or save as address #2
{snip}

 

[d0wnt0wn]

not quite what i am looking for tush but thanks..... I want to save the active sheet only not the workbook and although the name in c5 did appear in the save as box the save as box's default save location was not right

 

[tusharm]

Get the name right. Unless you want me to change the first four letters of your nick to d-u-m-b.

Posting edited.

not quite what i am looking for tush but thanks..... I want to save the active sheet only not the workbook and although the name in c5 did appear in the save as box the save as box's default save location was not right

 

[d0wnt0wn]

cant see why you would think that was funny but to each his own

 

[d0wnt0wn]

still looking for help with this