VBA - I cannot reference there is a Range using the Cells method - ERROR 400

Question

This first method gives me an ERROR 400

Worksheets("RELAT").Range(Cells(i, 3), Cells(i + 1, 12)).Value = Worksheets(mes).Range("P5:Y6").Value

This second method works normally

Worksheets("RELAT").Range("C7:L8").Value = Worksheets(mes).Range("P5:Y6").Value

The first and second methods are not the same things ? What’s wrong ?

Complete code:

Sub atualizar_Relatorio()
    
    Dim i As Integer
    Dim j As Integer
    Dim mes As String
     
    j = 1
    
    For i = 7 To 30 Step 2
     mes = ActiveSheet.Cells(i, j).Value    'pegando o mês na planilha
     
     MsgBox (mes & " " & i)     'testando o nome do mês na msgbox, código roda normalmente até aqui


     Worksheets("RELAT").Range(Cells(i, 3), Cells(i + 1, 12)).Value = Worksheets(mes).Range("P5:Y6").Value   'este não funciona
     Worksheets("RELAT").Range("C7:L8").Value = Worksheets(mes).Range("P5:Y6").Value      'este funciona

    Next i
    
    
End Sub

Summary of the code: I’m taking a range (values) of a spreadsheet (which I get the name (month) in the spreadsheet "RELAT" using the for) and I am copying the values of this in another spreadsheet using the cell reference, so that I can make the line change in the spreadsheet "RELAT" so that the data is not overwritten, but Excel shows me an Error 400.

Replaces the for by the following code, but wanted to reduce using the for

Code I’m using and it’s working:

Sub atualizar_Relatorio()

Worksheets("RELATÓRIO").Activate    

Worksheets("RELAT").Range("C7:L8").Value = Worksheets("JANEIRO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C9:L10").Value = Worksheets("FEVEREIRO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C11:L12").Value = Worksheets("MARÇO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C13:L14").Value = Worksheets("ABRIL").Range("P5:Y6").Value
Worksheets("RELAT").Range("C15:L16").Value = Worksheets("MAIO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C17:L18").Value = Worksheets("JUNHO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C19:L20").Value = Worksheets("JULHO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C21:L22").Value = Worksheets("AGOSTO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C23:L24").Value = Worksheets("SETEMBRO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C25:L26").Value = Worksheets("OUTUBRO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C27:L28").Value = Worksheets("NOVEMBRO").Range("P5:Y6").Value
Worksheets("RELAT").Range("C29:L30").Value = Worksheets("DEZEMBRO").Range("P5:Y6").Value

End Sub

Answer 1

You can’t mix the concepts of Range and Cells.

Range Selects a cell or an interval, the argument is also referenced to that used in native formulas, with letter to column and number to row. Examples: "A1" or "A1:B5".

Cells Select a specific cell, the argument is referenced with numbers. The first one refers to the row and the second one to the column. Example: "(1, 1)"

Therefore, it is not possible to reference one within the other in the way it expects, as an addressing.

---

The specific case:

There are other options, but I would suggest leaving the arguments of Range dynamics, merging variables (for rows) and direct references (for columns), similar to:

Worksheets("RELAT").Range("C" & i & ":L" & i + 1).Value = Worksheets(mes).Range("P5:Y6").Value

When executing the code, assuming that i = 7, we will have the construction:

Worksheets("RELAT").Range("C7:L8").Value = Worksheets(mes).Range("P5:Y6").Value