rubertu_22
05-20-2011, 08:08 AM
Hello!
I've been struggling with something I'm trying to achieve with a macro.
My goal is to use the template file to generate a new with the correct number each time I need to create a new estimate.
The file if it helps
https: // rapidshare.com / files/4051197975/Electric_Estimate.xlsm 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:="orçamento template", FileFormat:=52, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Save
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
So I open the template, and run the macro, what i want to for it do is to create a new file with the naming scheme thats above.
Electric Estimate.xlsm > run macro create file > Orçamento 0001_2011.xslx, and want the Electric Estimate.xlsm to be saved with the new increment for next use A8 changes by +1, so when I open Electric Estimate.xlsm the cell will be 0002, repete the process and I'll get a file named Orçamento 0002_2011.xslx and the Electric Estimate.xlsm will have the cell updated by +1 again, and so on. I can't get the increment in Electric Estimate to work. I've been changing the code based on what I find here and there, trying to solve, but no luck.
Thanks in advance.
I've been struggling with something I'm trying to achieve with a macro.
My goal is to use the template file to generate a new with the correct number each time I need to create a new estimate.
The file if it helps
https: // rapidshare.com / files/4051197975/Electric_Estimate.xlsm 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:="orçamento template", FileFormat:=52, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Save
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
So I open the template, and run the macro, what i want to for it do is to create a new file with the naming scheme thats above.
Electric Estimate.xlsm > run macro create file > Orçamento 0001_2011.xslx, and want the Electric Estimate.xlsm to be saved with the new increment for next use A8 changes by +1, so when I open Electric Estimate.xlsm the cell will be 0002, repete the process and I'll get a file named Orçamento 0002_2011.xslx and the Electric Estimate.xlsm will have the cell updated by +1 again, and so on. I can't get the increment in Electric Estimate to work. I've been changing the code based on what I find here and there, trying to solve, but no luck.
Thanks in advance.