Why is my copy/paste code firing twice each refresh?

[jrarmstrong]

Hi,

I've put together a simple macro to copy/paste data on each refresh. The problem is that it is running twice each refresh so duplicating data.

Can anyone see what the issue is?

Thanks

Code: Select all

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


[GaryRussell]

You are changing the worksheet in a worksheet change event which fires another event. Enclose your code that changes cells with the following statements.

Code: Select all

Application.EnableEvents = False
Change cells here
Application.EnableEvents = True

[Captain Sensible]

BA sends data to excel in two separate passes, one the price data and one maret data add If Target.Columns.Count <> 16 Then Exit Sub and your vba should only fire once.

Code: Select all


Private Sub worksheet_change(ByVal target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
....................rest of your code
 

[Captain Sensible]

Ooops didn't notice you'd taken into account the A:P, Do as Gary suggests

[jrarmstrong]

Thanks guys