I have a sheet which has a series alphanumeric references, the reference is always BISxxxx where xxxx is a four digit number starting at 0001 and progressing 0002, 0003 etc.

I have a button on my sheet which runs VBA code to add a new row with the new reference in the progression.

The part of my code that calculates the numeric part of the new reference is 

Dim OldRef As String
Dim OldRefNumber As String
Dim NewRefNumber As String

OldRef = Range("A" & NextRow - 1)
OldRefNumber = Right(OldRef, 4)
NewRefNumber = OldRefNumber + 1
In this instance I get the following

OldRef = BIS0001
OldRefNumber = 0001
NewRefNumber = 2

My problem is that I am losing the format of 000x, how to I make sure this is kept?

When I reach the tenth reference is needs to be 00xx and so on.

Hope this makes sense and someone can help me.

Ed