VBA Next Row Copy and Paste

[jamescooper]

Trying to copy data to another sheet upon H1 = Suspended, any ideas what I am doing wrong here?

Thanks.

Private Sub Worksheet_Calculate()


NextDataRow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1


If Range("H1") = "Suspended" Then
Range("AG1:BC1000").Copy Worksheets("Data").Range("B:X" & NextDataRow)


End If


End Sub

 

[mumps]

Maybe:

Private Sub Worksheet_Calculate()
    If Range("H1") = "Suspended" Then
        Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[jamescooper]

Thanks that worked, although it keeps pasting multiple times! I only want it to do it once!

Any ideas?

Thanks.

 

[Fluff]

Does this help

Private Sub Worksheet_Calculate()
    If Range("H1") = "Suspended" Then
        Application.Calculation = xlCalculationManual
        Range("AG1:BC1000").Copy Sheets("Data").Cells(Sheets("Data").Rows.Count, "B").End(xlUp).Offset(1, 0)
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub