Select sheet to work

[Leandroarb]

Is it normal for a code to work I have to insert a line to select the spreadsheet I want it to analyze?

Sub tst()
Dim matriz()   As String
Dim intLinhas As Integer
Dim rngVrp      As Range
Dim cel         As Range
Dim cont      As Integer

Sheets("Executados").Select 'IF YOU DO NOT WRITE THIS LINE DOES NOT WORK
cont = 0
Set rngVrp = Range(Cells(2, 1), Cells(WorksheetFunction.CountA(Worksheets("Executados").Range("A:A")), 1))
intLinhas = Application.WorksheetFunction.CountIf(Worksheets("Executados").Range("B:B"), 1) 'Conta o número de linhas
ReDim matriz(intLinhas, 13)

For Each cel In rngVrp
    If cel.Offset(0, 1).Value = 1 Then
        cont = cont + 1
            For k = 1 To 13
                matriz(cont, k) = Worksheets("Executados").Cells(cel.Row, k).Value
            Next k
    End If
Next

        For linha = 1 To intLinhas
            For coluna = 1 To 13
                Worksheets("Print").Cells(linha, coluna).Value = matriz(linha, coluna)
            Next coluna
        Next linha
Sheets("Print").Select
End Sub

 

[Fluff]

There's very rarely any need to select anything in VBA, you can do it like

Sub tst()
   Dim matriz()   As String
   Dim intLinhas As Integer
   Dim rngVrp      As Range
   Dim cel         As Range
   Dim cont      As Integer
  
   With Sheets("Executados")
      cont = 0
      Set rngVrp = .Range(.Cells(2, 1), .Cells(WorksheetFunction.CountA(.Range("A:A")), 1))
      intLinhas = Application.WorksheetFunction.CountIf(.Range("B:B"), 1) 'Conta o número de linhas
      ReDim matriz(intLinhas, 13)
     
      For Each cel In rngVrp
          If cel.Offset(0, 1).Value = 1 Then
              cont = cont + 1
                  For k = 1 To 13
                      matriz(cont, k) = .Cells(cel.Row, k).Value
                  Next k
          End If
      Next
     
              For linha = 1 To intLinhas
                  For coluna = 1 To 13
                      Worksheets("Print").Cells(linha, coluna).Value = matriz(linha, coluna)
                  Next coluna
              Next linha
   End With
   Sheets("Print").Select
End Sub

 

[Leandroarb]

There's very rarely any need to select anything in VBA, you can do it like

Sub tst()
   Dim matriz()   As String
   Dim intLinhas As Integer
   Dim rngVrp      As Range
   Dim cel         As Range
   Dim cont      As Integer
 
   With Sheets("Executados")
      cont = 0
      Set rngVrp = .Range(.Cells(2, 1), .Cells(WorksheetFunction.CountA(.Range("A:A")), 1))
      intLinhas = Application.WorksheetFunction.CountIf(.Range("B:B"), 1) 'Conta o número de linhas
      ReDim matriz(intLinhas, 13)
    
      For Each cel In rngVrp
          If cel.Offset(0, 1).Value = 1 Then
              cont = cont + 1
                  For k = 1 To 13
                      matriz(cont, k) = .Cells(cel.Row, k).Value
                  Next k
          End If
      Next
    
              For linha = 1 To intLinhas
                  For coluna = 1 To 13
                      Worksheets("Print").Cells(linha, coluna).Value = matriz(linha, coluna)
                  Next coluna
              Next linha
   End With
   Sheets("Print").Select
End Sub

Mr. Pluff, thank you very much by more this help and lesson!

 

[Leandroarb]

Other question, but i think this is personal, or not.
This the better way to make a report with excel and vba?
Technically speaking and thinking about performance.

 

[Fluff]

Without knowing what your data is like & what you re trying to do, it's very difficult to tell.
Also IMO there is no "best" way to do things, it's all very subjective.

 

[Leandroarb]

I understood.

Thank you!

 

[Fluff]

You're welcome.