Excell VBA quickly corrseponde the other in another spreadsheet

Question

need to run a macro depending on the selected cell. all the information is in the same column "E", but when it is for example for E4 I need the selected cell to correspond to another value in another sheet to run the same macro with the different cell value. following example.

Sheets("dados1").Select
    **Range("E6").Select** (esse range precisa ser dinamico a cada célula que selecionar na mesma coluna)
    Sheets("dados3").Select
    **Range("R6").Select** (esse muda junto com a mudança da primeira folha)
    Selection.Copy
    Range("X1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("dados1").Select

thank you in advance, I am beginner in vba

Answer 1

Speak Jairo! So for this you will need to vary the selected range, instead of making the I code select yourself. Then you need to identify the cell that is selected with the "Activecell" type:

Ai not know if I understood exactly what would be the "other cell in the other tab" you described. Example: if the selected cell is E6, in the other tab you want the R6 cell. If the selected cell is E7, in the other tab you want R7? Because with this we have a pattern and our code can adapt to this, it means that it will always do on the same line as the selected cell, makes sense? If that’s the case, you need to create a variable for that line and use it on both tabs. Then your code would look like this:

Sheets("dados1").Select
    linha = ActiveCell.Row 'esse codigo pega o numero da linha da célula selecionada atualmente
    Sheets("dados3").Select
    Range("R" & linha).Select 'esse codigo seleciona a celula da coluna R com a mesma linha da celula selecionada
    Selection.Copy
    Range("X1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("dados1").Select

If it is not the same line, you have to identify which is this pattern and create a variable for it, so your code will always work independent of the selected cell. Note: I used & to concatenate the texts, to join R with the value of the variable line. If you want to better understand this variable part I suggest this post here: https://www.hashtagtreinamentos.com/variaveis-no-vba-o-que-e-uma-variavel-no-vba/