VBA code does not want to run in the desired tab

Question

Good morning everyone, I’m sorry for the tongue inattention.

I’m trying to write a code that reads a table in the "Premises" tab. The table has 3 columns that I am saving in 3 vectors to build another table in a tab that I call "XX", in which I included the module. However, the code runs when I have selected the "Premises" tab, which does not suit me, while in the "XX" tab (where I am placing a button) the code stops in the following line:

Array_usina = Worksheets("Premissas").Range(Cells(4, 2), Cells(LR, 2)).Value

The part of the code I mention is below:

Sub gerar_dados()
Dim Array_usina() As Variant
Dim Array_circ() As Variant
Dim Array_trecho() As Variant


    Application.ScreenUpdating = False


        LR = Worksheets("Premissas").Cells(Rows.Count, 2).End(xlUp).Row 'last item in row #2
        Array_usina = Worksheets("Premissas").Range(Cells(4, 2), Cells(LR, 2)).Value
        Array_circ = Worksheets("Premissas").Range(Cells(4, 3), Cells(LR, 3)).Value
        Array_trecho = Worksheets("Premissas").Range(Cells(4, 4), Cells(LR, 4)).Value

What I’m doing wrong?

Answer 1

You’re taking the last line of the "XX" tab, try Worksheets("Premissas").Cells(Worksheets("Premissas").Rows.Count, 2).End(xlUp).Row and in cells within Range(), are also without reference Worksheets("Premissas").Cells(4, 2)

Or to improve the code and also make it more elegant, you can declare the spreadsheet and use a With:

Sub gerar_dados()
    Dim Array_usina() As Variant
    Dim Array_circ() As Variant
    Dim Array_trecho() As Variant
    Dim Premissas As Worksheet


    Application.ScreenUpdating = False
    On Error GoTo SairCodigo
    Set Premissas = ThisWorkbook.Worksheets("Premissas")

    With Premissas
        LR = .Cells(.Rows.Count, 2).End(xlUp).Row 'last item in row #2
        Array_usina = .Range(.Cells(4, 2), .Cells(LR, 2)).Value
        Array_circ = .Range(.Cells(4, 3), .Cells(LR, 3)).Value
        Array_trecho = .Range(.Cells(4, 4), .Cells(LR, 4)).Value
    End With

SairCodigo:
    Application.ScreenUpdating = True

    'Se houver erro, mostra mensagem
    If Err.Description <> "" Then
        MsgBox Err.Description, _
               vbExclamation + vbOKCancel, _
               "Error: " & CStr(Err.Number)
    End If

End Sub