What does this line of VBA do?

[rob51852]

Hi,

I’m trying to use a spreadsheet written by a predecessor. It is supposed to copy and paste data each time there is a change to Sheet1.

The problem is that each time a change is made the data is being pasted twice instead of once.

From my limited knowledge of VBA I think the problem may be in this line of code but I’m not clear on exactly what it does.

Private Sub worksheet_change(ByVal target As Range)
    Dim KeyCells As Range
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")
' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:P50")

If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then

Can someone explain what this does?

Thanks

Ps in case it is relevant the full code is below:

Private Sub worksheet_change(ByVal target As Range)
    Dim KeyCells As Range
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")
' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:P50")

If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then
          
'Count the cells to copy
Dim a As Integer
a = 0
For i = 5 To 100
If Sheets("Sheet1").Cells(i, 1) <> "" Then
a = a + 1
End If
Next i

'Count the last cell where to start copying
Dim b As Integer
b = 2
For i = 2 To 50000
If Sheets("Data").Cells(i, 1) <> "" Then
b = b + 1
End If
Next i

Dim c As Integer
c = 5
'Perform the copy paste process
For i = b To b + a - 1
If ThisWorkbook.Worksheets("Sheet1").Range("E2") <> "" And ThisWorkbook.Worksheets("Sheet1").Range("F2") = "" Then
Sheets("Data").Cells(i, 1) = Sheets("Sheet1").Cells(3, 14)
Sheets("Data").Cells(i, 2) = Sheets("Sheet1").Cells(2, 2)
Sheets("Data").Cells(i, 3) = Sheets("Sheet1").Cells(1, 1)
Sheets("Data").Cells(i, 4) = Sheets("Sheet1").Cells(2, 5)
Sheets("Data").Cells(i, 5) = Sheets("Sheet1").Cells(c, 26)
Sheets("Data").Cells(i, 6) = Sheets("Sheet1").Cells(c, 1)
Sheets("Data").Cells(i, 7) = Sheets("Sheet1").Cells(c, 6)
Sheets("Data").Cells(i, 8) = Sheets("Sheet1").Cells(c, 8)
Sheets("Data").Cells(i, 9) = Sheets("Sheet1").Cells(c, 15)
Sheets("Data").Cells(i, 10) = Sheets("Sheet1").Cells(c, 16)
Sheets("Data").Cells(i, 11) = Sheets("Sheet1").Cells(3, 2)
Sheets("Data").Cells(i, 12) = Sheets("Sheet1").Cells(c, 25)
c = c + 1
End If
Next i

End If

End Sub

 

[Norie]

You've not made it clear which line you want explained.

If it's this one then what it's doing is checking if the range Target refers to is within the range KeyCells, i.e. A1:P50, refers to.

If Not Application.Intersect(KeyCells, Range(target.Address)) _
           Is Nothing Then

However, that doesn't make much sense because Target will always be in that range because rather than referring to the range that's has been changed, as it should, Target is being set to refer to F2 here.

Set target = ThisWorkbook.Worksheets("Sheet1").Range("F2")

As for why data is being pasted twice, that's hard to tell.

Usually when that happens it means the code is calling itself because the code itself makes a change on the worksheet but as far as I can see that's not happening in the posted code.

 

[MARK858]

What happens if you make the changes in red below (untested)?

        Application.EnableEvents = False
        For i = b To b + a - 1
            If ThisWorkbook.Worksheets("Sheet1").Range("E2") <> "" And ThisWorkbook.Worksheets("Sheet1").Range("F2") = "" Then
                Sheets("Data").Cells(i, 1) = Sheets("Sheet1").Cells(3, 14)
                Sheets("Data").Cells(i, 2) = Sheets("Sheet1").Cells(2, 2)
                Sheets("Data").Cells(i, 3) = Sheets("Sheet1").Cells(1, 1)
                Sheets("Data").Cells(i, 4) = Sheets("Sheet1").Cells(2, 5)
                Sheets("Data").Cells(i, 5) = Sheets("Sheet1").Cells(c, 26)
                Sheets("Data").Cells(i, 6) = Sheets("Sheet1").Cells(c, 1)
                Sheets("Data").Cells(i, 7) = Sheets("Sheet1").Cells(c, 6)
                Sheets("Data").Cells(i, 8) = Sheets("Sheet1").Cells(c, 8)
                Sheets("Data").Cells(i, 9) = Sheets("Sheet1").Cells(c, 15)
                Sheets("Data").Cells(i, 10) = Sheets("Sheet1").Cells(c, 16)
                Sheets("Data").Cells(i, 11) = Sheets("Sheet1").Cells(3, 2)
                Sheets("Data").Cells(i, 12) = Sheets("Sheet1").Cells(c, 25)
                c = c + 1
            End If
        Next i
        Application.EnableEvents = True