VBA to Increment Invoice Number

[JAlbers]

I am having some difficulty getting a routine to work and I am very new at trying to write macros so I don't quite understand what it means when it bombs out.

What I am attempting to do is to write a macro that will increment the last number by 1, combine the current year to the number and enter this combined number to a cell in the spreadsheet.

The number (less the current year part) would be written back and saved with the spreadsheet in an out of the way place to use the next time it was needed, then the current date would be entered into the date cell with a copy paste value to keep the =now() function from updating if the file is reopened.

Here is the code (and I use the term loosely) I have so far:

Sub New_MTR_Number()
LastMTR = Worksheets("OldMTRNumber").Range("A1").Value
NextMTR = LastMTR + 1
MTRNumber = "=year(now())" & "NextMTR"
'this appears to be where the problem lies
Worksheets("MTR").Range("D6").Value = MTRNumber
'
Worksheets("OldMTRNumber").Range("A1") = NextMTR
ActiveWorkbook.Save
Range("J6").Select
Selection.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("J6").Select
Application.CutCopyMode = False
Range("c10").Select
End Sub

Thanks for this forum and all the knowledge that roams these halls!

 

[AlphaFrog]

I'm not sure I follow exactly what you want to do but here's a guess.

Sub New_MTR_Number()

    Dim NextMTR As Long

    NextMTR = Worksheets("OldMTRNumber").Range("A1").Value + 1
    Worksheets("OldMTRNumber").Range("A1").Value = NextMTR
    Worksheets("MTR").Range("D6").Value = Year(Date) & NextMTR
    ActiveWorkbook.Save
    
    Range("J6").Value = Date
    Range("C10").Select
    
End Sub

 

[JAlbers]

Wow! Thanks! That worked just the way I was shooting for. It seems I have a long way to go to be able to get something so concise and simple.

Thanks again!