PDA

View Full Version : VBA event to detect an Autofill Scrolling?



temujin
01-24-2018, 01:39 AM
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
01-24-2018, 05:14 AM
Welcome to the forum!

You could probably do it using the Worksheet_SelectionChange instead.

temujin
01-24-2018, 07:07 AM
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
01-24-2018, 07:52 AM
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