I’m starting in VBA, can you help me?

Question

I need to make a code that when I click on a button (in the "MARC" spreadsheet), it copies the information in the row (which would be a variable) and columns A through G, and leads to another spreadsheet with the name "PROGR. DIARIA", look for the last line and paste in the first empty line after the last line with data, I’ll leave what I tried to do here

Sub Linha3_MARC()    
'   
' Linha3_MARC Macro   
'   
'

    Range("A3:G3").Select 
    Selection.Copy
    Sheets("PROGR. DIARIA").Select            
    Range("A2").Select     
    Selection.End(xlDown).Select        
    ActiveSheet.Paste  
End Sub

But I can’t get them to skip one more line after the selection.end(xldown).select

Answer 1

Good morning Lucas,

The code below should solve your problem. Basically, I created two variables to manipulate the two tabs and a variable that stores which was the last line with values filled in the "PROG.DIARIA tab".

I did not insert any repeating structure, because, from the description of the problem, it did not seem to need.

What was missing in the code to skip the line was the "Offset", which moves the cursor through the spreadsheet.

Dim W           As Worksheet
Dim WB          As Worksheet
Dim UltCel      As Range

Set W = Sheets("MARC")
Set WB = Sheets("PROGR. DIARIA")

W.Select

'Seleciona o intervalo de A3:G3 na aba MARC e copia os valores
W.Range("A3:G3").Select
Selection.Copy

WB.Select
WB.Range("A2").Select
Set UltCel = WB.Cells(WB.Rows.Count, 1).End(xlUp)

WB.Range("A" & UltCel.Row).Select
ActiveCell.Offset(1, 0).Select
WB.Paste

'Volta para a planilha inicial
W.Select
W.Range("A3").Select

msgbox "Pronto"

Just one question: what did you mean by "...on the line(that would be a variable)..."?

I kept the "copy and glue" tab "MARC" fixed in the "A3:G3" range, because, from what I saw, in this tab, there would be no more lines below or above to copy and paste. If you do, answer here and I’ll change the code.

I hope it helps!