Tearing My Hair Out

[CordeliaEarle]

Hope someone can tell me what I am doing wrong.

I have copied and pasted from this web page: https://www.mrexcel.com/news/next-invoice-number/

and have corrected all the cell numbers to do with my saving details, and the macro is perfect. BUT, when I then go in to the invoices again, to invoice some more at the end of the next month I always get the following error:

https://picmonkey.space/invite/36vRandbvJwPbDV5YoJPpW

Please tell me why this happens.

 

[CordeliaEarle]

Right this is the coding, apparently my picture won't share!!!

Sub NextInvoice()
Range("E4").Value = Range("E4").Value + 1
Range("B14:D34").ClearContents
End Sub


Sub SaveInvWithNewName()
Dim NewFNAsVariant
'CopyInvoicetoanewworkbook
ActiveSheet.Copy
NewFN = "D:\PetSitting\Invoices" & Range("E4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice


The red line is the one that comes up in the debug

 

[Fluff]

Hi & welcome to MrExcel
What error number & message do you get?

 

[CordeliaEarle]

Thanks for coming back to me, and it's great to be here.

I hope I have found a way now for the screen shots to show up!!

 

[Fluff]

Should the file be called Invoices1.xlsx, or should it be 1.xlsx, in a folder called Invoices

 

[CordeliaEarle]

The file should call itself Invoices1.xslx, which it does perfectly when I set up the Macro and then saves consecutively. Then the next month when I go to enter the next lot of invoices I use the macro enabled template and it then starts doing this. Is it because I am opening the template and not the last invoice? I really hope I'm making sense.

Cx

 

[Fluff]

Try

Sub SaveInvWithNewName()
   Dim NewFN As String
   'CopyInvoicetoanewworkbook
   ActiveSheet.Copy
   NewFN = "D:\PetSitting\Invoices" & Range("E4").Value & ".xlsx"
   ActiveWorkbook.SaveAs NewFN, 51
   ActiveWorkbook.Close
   NextInvoice
End Sub

But if E4 reverts back to 1 at the start of each month, you'll have a problem as the file already exists.

 

[CordeliaEarle]

hi Fluff,

No the file doesn't revert back.

So as I've been tearing my hair out, lol, I've been manually saving and adding the next invoice number. If I put this code in to the template file, when I have closed program, shut down, stopped the world etc, which file do I open again to start the next time? The template or the xls file?

Do I just start again with a new template file and start with my next invoice number will that work, rather than trying to re-code the bugged one?

Cx

 

[Fluff]

If the template is an .xlsm file, then just keep using that.

 

[CordeliaEarle]

Nope, arrrrggghhhh, I don't want to have to throw anything out!!!!