Auto-Number for PO spreadsheet

Hi All

I have just set up a new Purchase Order spreadsheet for work, but am struggling with the auto-numbering.

I want the PO numbers to been in the format of 2016001, 2016002, 2016003 (as shown), and to show the newest at the top/.

I want to be able to press the 'new po number' button, and a new blank row appear (in Row 4).

I've attached a quick screenshot for you.

Would be grateful for any assistance.

Thanks

SarahPO.png

Try this:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "A4" Then
    Target.EntireRow.Insert
    Target.Offset(-1) = IIf(Target = "", 2016000, Target + 1)
End If
End Sub

Try this:-
Right click sheet tab, Select "View Code", Vbwindow appears, Past code in window, Close Vb window
Code runs when you click "A4"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "A4" Then
    Target.EntireRow.Insert
    Target.Offset(-1) = IIf(Target = "", 2016000, Target + 1)
End If
End Sub

Hi All

All works great, except that it copies the format of the row above, where the row colour is yellow :s

Perhaps this:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "A4" Then
    Target.EntireRow.Insert
    Target.Offset(-1).Interior.ColorIndex = xlNone
    Target.Offset(-1) = IIf(Target = "", 2016000, Target + 1)
End If
End Sub

Change line as below:-

Target.Offset(-1).EntireRow.Interior.ColorIndex = xlNone