Macro to copy and put using VBA Excel

Question

I am learning VBA to streamline my service in the company, but I am stuck in the following part:

I was developing a macro that in theory would make the following path:

• 1° Move mouse to A2 cell
• 2° Copy A2 cell content
• 3° Paste that content into a specific notepad

As it is in cell A2, the mouse position would move to bar and would give 1 click, moving to Cell A3 and loop in the process.

I managed to do the mouse process, own hotkey. But when it arrives at the copy part, it is selecting 2 cell and consequently giving error for not being able to make 2 cell at the same time... In addition to CTRL V that is not working...

Anyway, if anyone can help me or develop for me would be very grateful. It would help me to see where I was going wrong and help me in learning.

Answer 1

I’m not sure I understand exactly your need. What I caught was that you want to copy a content from a cell and paste it into the cell below. It’s just not clear to me until when would be the loop. As I didn’t understand correctly I made two possible scenarios.

This code only inserts the value of A2 in A3.

Sub copiarColarSimples()

    'Seleciona a planilha e range em que esta a informação
    Worksheets("Planilha1").Select
        
    'Cola a informação de A2 em A3
    Range("a3").Value = Range("a2").Value
   
End Sub

This loop copies the data from a cell to every cell below until it finds a cell that has data. That is, it will copy the data from A2 to A3, after A3 to A4 until a cell below already has data.

Sub copiarColarLoop()


    Worksheets("Planilha1").Select
    
    Dim linha As Integer
    Dim i As Integer

    linha = 2
    i = 1
    
    While Cells(linha + 2, 1) = ""
    
        Cells(linha + 1, 1).Value = Cells(linha, 1).Value
        linha = linha + 1
    
    Wend
    
End Sub

Before the execution of the Loop

After the Execution