Don't want to overwrite name; create new with a 2 on the end

Status
Not open for further replies.

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I have this code now:

Code:
Sub SaveToSidonna()
'
' Print_And_Save Macro
'
'

'
    Dim strPath As String
    Dim wb As Workbook
    
    Application.ScreenUpdating = False
    Sheets("ESTIMATING").Select
    ActiveWorkbook.Save
    Path = "\\Sidonna\c\Estimating\"
    'Path2 = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"
    ActiveWorkbook.SaveAs Filename:= _
        Path & _
Range("B11").Value & ".xls", FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    new_file = ActiveWorkbook.Name
    Range("F2").Select
    'Workbooks.Open Filename:=Path2 & "EstimatingSheetTest.xls"
    strPath = "C:\Documents and Settings\Primary User\Desktop\Toms Main\"
     If Dir(strPath & "EstimatingSheet.xls") <> "" Then
        Set wb = Workbooks.Open(strPath & "EstimatingSheet.xls")
     End If

     strPath = "C:\Documents and Settings\Owner\My Documents\Daily\"

     If Dir(strPath & "EstimatingSheet.xls") <> "" Then
        Set wb = Workbooks.Open(strPath & "EstimatingSheet.xls")
     End If
    Workbooks(new_file).Save
    Workbooks(new_file).Close
    Application.ScreenUpdating = True
End Sub

It works well :oops: :oops: :oops:

IF a file exsists with the same name more than once, it gives a message:
"File Already exsists, would you like to overwrite or cancel"

Is there anyway I can just add a 2, 3, or 4 to the name of the file? Depending on how many files with the same name in Cell B11.
Example would be:
Walter Residence
If it sees a Walter Residence it would then automatically name the next file
Walter Residence 2 then Walter Residence 3 and on and on and on...

Does this make sense? :confused: :confused:

Thank You for any assistance, :confused:
Michael
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Right after this line:

Code:
ActiveWorkbook.SaveAs Filename:= _
        Path & _
Range("B11").Value & ".xls", FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False  new_file = ActiveWorkbook.Name

What can I add to Instead of "If file already...blah blah blah"
Have a message box with "Your file exsists, would you like to create another copy?"
Then add a number to the file name.

Michael
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,216,028
Messages
6,128,399
Members
449,447
Latest member
M V Arun

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
Back
Top