Copy cells when identifying Worksheet_change change (Excel VB)

Question

I am new in VB and I am trying to make an operation that copies some information from the spreadsheet "Control Backup Clients" to the spreadsheet "LOG". The process will be as follows: Excel when detecting change in any cell of the column N of the sheet "Control Backup Clients", should copy the respective value of column A (called client code) and paste in the next blank line of the sheet "LOG", where will be recorded all the changes made.

Below follows the code I did, but every time it presents error of "Required Object" 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

Answer 1

Hail,

After a lot of research and study, I was able to solve my problem. I will 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 you have the client’s general data (codcli, name, cnpj, etc.), and the other is the Log spreadsheet. What happens is that in the Clients spreadsheet I have the column N where I enter a date, referring to which day the customer problem has been fixed. What I needed was that when this information was inserted in column N, I would take the client code (column A) of the respective row, date of today(Now), the value inserted in column N and the user, go to sheet "LOG" and save the information in the sequence from column A and the next available row. Below follows the code that performs exactly this.

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(stores the user name) is empty, open an inputbox to enter the name. I will still implement other things, such as asking user and password when opening the spreadsheet to have a greater security but this needs a little more study rs.

I hope you help someone out there. Hug