Find the last Inv No. On Invdata sheet and copy to a cell

Hi Dott and welcome to the Forum

In the attached file, I've made two sheets, the second (InvData) containng a list of dates and some imaginary 4-digit invoice numbers.

On the first sheet, there's a green shape captioned "Start new invoice". I used right click/ Assign macro.... and choose the macro NextInv from the list (having already created that in Module1). The code for that is below (with explanatory comments) and runs when you click the shape (button):

Sub NextInv()

    Dim LargestInv As Long
    On Error Resume Next

    ' check invoice date field F9 is clear
    If Range("F9").Value <> "" Then
        MsgBox "Please clear F9 before starting new invoice"
        Exit Sub
    End If

    'get biggest invoice number in column B (2)
    LargestInv = WorksheetFunction.Max(Worksheets("InvData").Columns(2))

    ' add to F9
    Range("F9").Value = LargestInv + 1
    'add today's date to cell B9
    Range("B9").Value = Date

End Sub

There are several ways to get the highest number in VBA but here I've used the Excel function Max (which you may have used in a cell)- that gets the highest number (in case your invoices in InvData have been sorted) and add 1 to it.

The macro tells you if there's a number already in F9 (I assume you'll later write a macro to copy invoice data to InvData and clear it) and otherwise adds today's date (using the VBA function Date) along with the invoice number. 

Hope this makes sense and fixes your problem. If so. please remember to mark this Answer as Selected.