VBA event to detect an Autofill Scrolling?

[temujin]

Hi,

I am relatively new working with VBA and I am having some troubles writing a macro. I have a Workbook with two worksheets. In the first one I introduce some data and the second one is some sort of backup worksheet where changes in some columns in the first one are saved.

I was able to program a script to detect Worksheet_Change so if I rewrite a single cell, the new data are also copied in the backup worksheet. The problem is that oft I need to do Autofill (click right-down and scroll) when I want to use the same formula in many cells and this is not detected by the Worksheet_Change routine. Is there anyway to detect this Autofill event so I can programm the require action?

Thank you very much

[paulked]

Welcome to the forum!

You could probably do it using the Worksheet_SelectionChange instead.

[temujin]

Welcome to the forum!

You could probably do it using the Worksheet_SelectionChange instead.

The problem with Worksheet_SelectionChange is that it detects every time I select one or more cells. I want the code to be executed only when I do Autofill, no by selecting cells.

[georgiboy]

How about this:

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim r As Range


    If Target.Address = Cells.Address Then
        MsgBox "Can't select all cells", vbCritical, "Error"
        Application.Undo
        Exit Sub
    End If
    
    If Target.Cells.Count > 1000 Then
        MsgBox "Can't select more than 1000 cells", vbCritical, "Error"
        Application.Undo
        Exit Sub
    End If
    
    For Each r In Target.Cells
        Sheet2.Range(Target.Address).Value = r.Value
    Next r
 
End Sub

Hope this helps