Copy and paste cell when changed

Asked

Viewed 1,532 times

4

I’m starting to use VBA with the office package, as I still know little I’m having difficulties to get this functionality.

I was trying to do something simple and I needed help to understand how it works:

I need that if one cell is changed, its contents are copied to another automatically, without having to click any button, e.g.:

I have cell A1:B5

All cells are blank, if I fill in any information in cell A1, I need the value of it to be copied to cell B1, if I change cell A2, I need the value to be changed to cell B2 and so on.

I know you can do this with formulas, but I need the VBA syntax to start doing something more advanced.

excel vba excel-vba

  • One solution is this, if you need the code quickly, surely you have a solution if you program. Developer of Excel and goes on record Macro. Then solve your problem and using formula and stop the recording. Finally edit the recorded macro and catch the code contained therein.

  • Use Excel events such as Worksheet_Change, that is triggered when there is some change in the desired spreadsheet. Then program a condition If, that if the Target for in the column To, the value of this will be copied to the column B.

  • 1

    If B1 gets the content of A1 why not simplify and put in B1 the formula =A1 ?

1 answer

2

Insert Event code

Use the event Worksheet_Change, where the data shall be placed within the worksheet in which the data is located. For example, in my case it was in Planilha1:

Planilha1

Code for a cell

The code is triggered every time the sheet has some change and has a condition that if the change is made in column A, then copies the values.

 Option Explicit
    Private Const B As Long = 2                  '<-- Coluna B
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False
    Dim Coluna As Long                           '<-- Coluna selecionada
    Dim linha As Long

    Coluna = Target.Column
    'Se ocorrer mudanças na coluna A
    If Coluna = 1 Then
        linha = Target.Row
        Cells(linha, B) = Cells(linha, Coluna)
    End If

CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Code for multiple cells

Option Explicit
    Private Const B As Long = 2                  '<-- Coluna B
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False
    Dim Coluna As Long                           '<-- Coluna selecionada
    Dim linha As Long
    Dim Area
    Coluna = Target.Column

    'Caso mais de uma célula seja alterada
    If Target.Count > 1 And Coluna = 1 Then
        Dim rng As Range
        Set rng = Range(Target.Address)
        For Each Area In rng.Areas
            Area.Offset(, 1).Value = Area.Value
        Next
        GoTo CleanExit
    End If
    'Se ocorrer mudanças na coluna A
    If Coluna = 1 And Target.Count = 1 Then
        linha = Target.Row
        Cells(linha, B) = Cells(linha, Coluna)
    End If

CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.