Excel  VBA sum +1 line

I have a question.
I've been testing the macros to fill a database, but I'm having a problem.
ID  Name
ZZ1  A
ZZ2  B
How can I add this automatically? (Now ZZ3) I have the following code that works with only numbers. I've tried various forms but it always makes a mistake because of the variable.
Sub novo() Dim codigo, x As Double
'Descobre a ultima linha
x = Sheets(2).Cells(Rows.Count, 1).End(xlUp).RowIf x = 1 Then
codigo = x
Else
codigo = Sheets(2).Cells(x, 1) + 1
End If
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo
'Limpar campos
End Sub

EDIT:
Regex
Enable Regex in Excel
 RegEx needs to be enabled,
Standard
A simple Regex that identifies if you have numbers
\d+
and if you have letters without special characters[azAZ]+
Code in Excel VBA
Dim codigo As String, x As Long Dim objMatches As Object, objRegExp As Object Set objRegExp = CreateObject("VBScript.RegExp") 'Regex objRegExp.Pattern = "\d+" objRegExp.Global = True
'Descobre a ultima linha
x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).RowIf x = 1 Then
codigo_novo = x
Else
codigo = Sheets(2).Cells(x, 1)
Set objMatches = objRegExp.Execute(codigo)
If objMatches.Count <> 0 Then
For Each m In objMatches
numero = CLng(m) + 1
Next m
End If
objRegExp.Pattern = "[azAZ]+"
Set objMatches = objRegExp.Execute(codigo)
If objMatches.Count <> 0 Then
For Each m In objMatches
letras = m
Next m
End If
End Ifcodigo_novo = letras & numero
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo_novo
Original answer
1  Declarations
This way
Dim codigo, x As Double
you are only declaring x as Double and code is like Variant, that Excel tries to find the variable type of data (date type). The correct would be:Dim codigo As Double, x As Long
Use the variable Long to get the last line, as rows are whole numbers, but if you declare as Integer, errors will occur if passing 32767 Lines. Long can go to 2147483647
2  Last Line
You are not specifying the worksheet correctly to find the last line, you can perform this in various ways. Here’s the example of two:
x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).Row
Or
Dim ws2 As Worksheets
Set ws2 = ThisWorkbook.Sheets(2)
With ws2
x = .Cells(.Rows.Count, 1).End(xlUp).Row
'Outros códigos com a Planilha2 podem ser usados com .Cells, sem especificar a planilha em questão
End With
3  Soma
You are performing an operation to sum the cell value with 1, so if you want to sum the line number:
codigo = Sheets(2).Cells(x, 1).Row + 1
But if you want to sum the cell value and it is as text, try to convert to Double first:
codigo = CDbl(Sheets(2).Cells(x, 1)) + 1
To add Letters, join two Strings:
codigo = CStr(Sheets(2).Cells(x, 1)) & "1"
Or if there are also Letters in this column, one can check if the cell is a number:
Dim codigo As Double, x As Long
'Descobre a ultima linha
x = Sheets(2).Cells(Sheets(2).Rows.Count, 1).End(xlUp).RowIf x = 1 Then
codigo = x
Else
If IsNumeric(CDbl(Sheets(2).Cells(x, 1).Value)) Then
codigo = CDbl(Sheets(2).Cells(x, 1).Value) + 1
Else
codigo = xlErrNA
End If
End If
'Adiciona o novo Código
Sheets(1).Range("F5") = codigo
'Limpar campos
 RegEx needs to be enabled,