Renaming workbook

[Useless1]

Hi
I would like to find a way to have my invoice workbook automatically rename using 2 different cells, one of which is an invoice number in cell N6 generated using this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("N6").Value = Range("N6").Value + 1
End Sub

the other being text in cell N11. The renamed workbook should look like "Inv N6-N11.xls"
Its probably best if this happens when saving the workbook.

Thanks in advance

 

[pboltonchina]

Try this

ActiveWorkbook.SaveAs filename:="C:\" & Range("N6") & "-" & Range("N11") & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

You can change C:\ to a path to suit you

 

[Useless1]

Thanks pboltonchina, unfortunately the problem has become more complex. I managed to solve the saving and renaming with this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Me.SaveAs "Inv" & " " & Range("N6").Value & "-" & Range("N11").Value & ".xls"
Cancel = True
Application.EnableEvents = True
End Sub

Which works perfectly, however the automatic sequential inv no. doesnt work as it saves the higher no. to the new file rather than the old template, to do what I want I need some code that will fit in the above sub that opens the template "Inv Template.xls" adds 1 to the N6 cell and then saves and closes it. Is this possible?

Thanks

 

[Andrew Poulsom]

See if this helps you:

http://www.mrexcel.com/forum/showthread.php?t=49835

 

[Useless1]

Thanks Andrew, the thread you directed me to has an auto generated no. upon opening, I think I need the number generated at the save point, which is why I thought it might fit into the code above. Sorry i'm a true novice at this and need very precise instructions. p.s. am on a mac if that makes a difference.

 

[Andrew Poulsom]

Isn't it just a matter of incorporating the number within your Workbook_BeforeSave event procedure?

 

[Useless1]

Hi Andrew

I think if I do that then every save will be 1 number higher than the template, I.E. all the same.

So I thought that in the Beforesave sub I could add code that opened "inv template.xls", added 1 to the cell, saved as "inv template.xls", replacing original, then closed. This would have to happen after the rename in the sub.

Thanks

 

[Andrew Poulsom]

The code in the link I posted puts the next number in A1. Can't you use that in the file name? You don't need to save the template.

 

[Useless1]

hi andrew tried that code on a new workbook to see how it worked and got

runtime-error '424':
object required

this is the line highlighted in debug

FName = ThisWorbook.Path & Application.PathSeparator & "Number.Txt"

thanks

 

[Andrew Poulsom]

Yes, there's a typo:

FName = ThisWorkbook.Path & Application.PathSeparator & "Number.Txt"