Excel form - incompatible types

Question

I know this should be a simple question to answer, but I’m starting to program in VBA now. The code below should transfer the data from one spreadsheet to another, should be something very simple, but gives compilation error.

Sorry if I used some command that does not exist in VBA, but I’m really here to learn.

(The code is associated with a button)

Sub Submit()

Dim rLast As Long

With Sheets("Plan2")
 .Cells("B", 2).Select.Copy

End With  


With Sheets("database")

  'Obtém a última linha da Planilha:
   rLast = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
  .Cells(rLast, 1).Select.Paste


End With
End Sub

Answer 1

When using .Cells only rows and columns should be used numerically and using with always leave the commands on separate lines as below.

Another correction in your formula is that you are using the copy to a spreadsheet Sheets().

Your function will work with the following fixes:

Sub Submit()

Dim rLast As Long

    Sheets("Plan2").Range("B2").Copy

    With Sheets("database")
        'Obtém a última linha da Planilha:
        rLast = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(rLast, 1).Select
        .Paste    
    End With
End Sub

Answer 2

Your syntax of .Cells is incorrect, where the correct is .Cells(Linha, Coluna). There is no need to use only numbers, because lastrow = db.Cells(db.Rows.Count, "A").End(xlUp).Row also returns the last line of the sheet "database"

But there is a simple and practical way to copy and paste

'Declaração de variáveis, para duas ou mais cada Data Type deve ser escrito novamente
'Caso não seja escrito, a mesma é declarada como Variant
Dim db As Worksheet, ws2 As Worksheet

Set db = ThisWorkbook.Sheets("database")
Set ws2 = ThisWorkbook.Sheets(2)
'Pode usar .Sheets ou .Worksheets, com o nome entre "" ou com o número de index
rLast = db.Cells(db.Rows.Count, 1).End(xlUp).Row + 1

ws2.Range("B2").Copy Destination:=db.Cells(rLast, 1)

In which the method Range.Copy can be seen in the link and the example of this page was used.