Copy Cells When Identifying Change Worksheet_Change (Excel VB)

Question

Copy Cells When Identifying Change Worksheet_Change (Excel VB)

Navigation

#1 by (1 votes)

1

I am new to VB and am trying to get an operation that copies some information from the "Client Backup Control" worksheet to the "LOG" worksheet. The process will look like this: When detecting change in any cell in column N of the "Client Backup Control" worksheet, Excel should copy its value from column A (called client code) and paste it into the next blank line of the "LOG" worksheet, where all the

BelowisthecodethatIdid,buteverytimeIgeta"Required Object" error and I can not find the problem

Dim celChave As Range
Dim codCliente As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    'celChaves onde fica guardado as celulas que serão alteradas
    Set celChave = Sheets("Controle Backup Clientes").Range("N2:N1048576")

    If Not Application.Intersect(celChave, Range(Target.Address)) _
           Is Nothing Then

        codCliente = Sheets("Controle Backup Clientes").Select(0, 1)
        Sheets("LOG").Select(1048576, 1).End(x1Up).Offset(1, 0).Paste
        Application.CutCopyMode = False
        Sheets(Sheets("LOG").Select(1048576, 1).End(x1Up).Offset(0, 1)).FormulaR1C1 = "=TODAY()"
        Sheets(Sheets("LOG").Select(1048576, 1).End(x1Up).Offset(0, 2)).Paste = codCliente


        MsgBox "CORREÇÃO SALVA NO LOG! CELULA (" & Target.Address & ")"

    End If 
End Sub

excel-vba

asked by anonymous 07.06.2018 / 19:25

1 answer

Query to count table dependencies auto zoom in the image

score 1 · Accepted Answer

Save,

After much research and study, I was able to solve my problem. I'll explain in more detail the problem and the solution.

I have two spreadsheets, one Clients (I bring this listing of a database in SQL), where it has the general data of the client (codec, name, cnpj, etc), and the other is the Log worksheet. What happens is that in the Customers worksheet I have the N column where I enter a date, referring to which day the customer problem was corrected. What I needed was that when I entered this information in column N, I would get the customer code (column A) of the respective row, today's date (Now), the value entered in the cell of column N and the user, go to the "LOG" worksheet and save the information in the respective sequence from column A and the next available line. Below is the code that does just that.

Public wsLog As Worksheet
Public nUsu As String

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("N1:N1048576")) Is Nothing Then
        Application.EnableEvents = False
        Dim Rng As Range
        Set wsLog = Sheets("LOG")
        Dim codCli As Variant
        Set Rng = wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1)
        If nUsu = "" Then
            nUsu = InputBox("Digite seu nome!")
            MsgBox "Olá " & nUsu & "!"
        End If
        codCli = Target.Offset(, -13).Value
        With Rng
            .Value = codCli
            .Offset(, 1).Value = Now
            .Offset(, 2) = Target.Value
            .Offset(, 3) = nUsu
        End With
    End If
    Application.EnableEvents = True
End Su

b

I also made some validations, for example if changing the cell in column N and nUsu (store the user name) is empty, open an inputbox to enter the name. I'm still going to implement other things, like asking for username and password when opening the worksheet to have a higher security but this needs a little more study rs.

Hope you can help someone there. Hugs