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

 

[Useless1]

hi andrew
that works fine now, however it generates a new number every time the template is opened and any invoices that i save the invoice number goes up every time I open them, as I'm prone to errors this would be often . That is why I was hoping for a bit of code to open,change,save,close the other workbook. I think this should be possible as I have seen similar.

Thanks

 

[Andrew Poulsom]

If you want to edit an existing invoice there are a number of things you can do:

1. Hold down Shift when you open the workbook, so that the Workbook_Open event doesn't fire.

2. Change the Workbook_Open procedure so that it increments the invoice number only if the cell that contains it is empty.

3. In a spare cell in the template put TEMPLATE. Change the Workbook_Open procedure to check for that and increment the invoice number only if it's present. When the invoice number has been incremented clear the contents of the cell.

 

[Useless1]

That would work, but as I'm only trying to save the effort of manually inputting the new number every time, this way probably creates more problems than it solves

 

[Useless1]

A bit like your option 3, is it possible to have the code delete itself after opening so only the template retains the incremental increases and the saved file is cleared??? I'm guessing this is complicated but it sounds like the answer.

 

[Andrew Poulsom]

You could move the worksheet to a new workbook and save that.

 

[Useless1]

That would work, can it be done as a part of the code below so that its automatic upon save

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


Thanks

 

[Useless1]

This seems to have done it

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
ActiveSheet.Name = "Inv" & " " & Range("N6").Value & "-" & Range("N11").Value & ".xls"
With Destwb
.SaveAs "Inv" & " " & Range("N6").Value & "-" & Range("N11").Value & ".xls"
.Close SaveChanges:=False
End With
Cancel = True
Application.EnableEvents = True
End Sub


Its easy when it works

Thanks for your help