Macro "Saving as" that keeps orignal file name and gets updated

[rubertu_22]

Hello!

I need some help, this is the current macro I'm using, what I managed to get so far.

Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A7").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = "Orçamento 000" & fName & "_" & Format$(Date, "yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"D:\Users\Robby\Documents\Gigawatt\ELect"
ActiveWorkbook.SaveAs Filename:=newFile

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sales Receipt").Range("a7").Value = Worksheets("Sales Receipt").Range("a7").Value + 1
End Sub

Private Sub Workbook_Open()

End Sub

What I'm trying to achieve is to have the main file edited and when i save that file, the changes are saved to another file which gets a estimate number, Orçamento 0002_2011, Orçamento 0003_2011, and so on, this new file is final so it can't have the macro being transported with it. The initial file(template) need to update the estimate number for next use.

Resuming
1st use
Template(cell as the number 001) file ->edit file-> run macro -> create file bla_bla_001_2011, and updates template cell(count of estimates) to 002.
2nd use
Template(cell as the number 002, updated on the previous use)-> edit file-> run macro -> create file bla_bla_002_2011, and updates template cell(count of estimates) to 003.
....
and so on.

Thanks in advance ,

 

[rubertu_22]

excel file
googledocs
http://tinyurl.com/63l6x9p
rapidshare
http://tinyurl.com/5se7nru

 

[rubertu_22]

updated code so far,

Sub SvMe()
     'Saves filename as value of A1 plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("A8").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = "Orçamento 000" & fName & "_" & Format$(Date, "yyyy")
     ' Change directory to suit your PC, including USER NAME
    ChDir _
    "D:\Users\Robby\Documents\Gigawatt\ELect"
    ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sales Receipt").Range("a8").Value = Worksheets("Sales Receipt").Range("a8").Value + 1
End Sub

help, please

 

[rubertu_22]

Hello,

Managed to solve the issue I was having, leave my solution here, in case anyone hangs on something similar.

Sub faznovoorçamento()
    'Adds +1 to A8, and saves template file with the new value of A8
    Application.DisplayAlerts = False 'Disable alert prompt
    Worksheets("Sales Receipt").Range("a8").Value = Worksheets("Sales Receipt").Range("a8").Value + 1
    ThisWorkbook.save
    
    'Converts the date(=today()) as values for the new file
    ThisWorkbook.Sheets("Sales Receipt").Range("B5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'deletes the save button
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    
    'Creates new file with the new value of A8, saves it with no macros
    Dim newfile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("A8").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newfile = "Orçamento 000" & fName & "_" & Format$(Date, "yyyy")
     ' Change directory to suit your PC, including USER NAME
    ChDir _
    "D:\Users\Robby\Documents\Gigawatt\ELect"
    ActiveWorkbook.SaveAs Filename:=newfile, FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
   Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
End Sub

The final codes has 2 extras set of code in it, was useful for me, so I leave it to you criteria.

Regards
Roberto