Copy and paste

[Rob]

Hi,

The code below loops through rows checking two offset cells to the right of
the active cell until an empty row is found it then resets to the top row two
cells offset from the original active cell. Should any of the rows reach a
specific criterion a User Form shows with a text box showing the letters in
(“b†& target row). After sixty seconds the process starts over again.


Sub CheckVolumeRise()
StartTimer1
Do
If ActiveCell<ActiveCell.Offset(0,2) And ActiveCell.Offset(0,2) < _
ActiveCell.Offset(0, 4) Then
CriteriaReached.Show
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop Until IsEmpty(ActiveCell)
ActiveCell.Offset(-1, 0).Activate
Selection.End(xlUp).Select
ActiveCell.Offset(0, 2).Activate
End Sub

I would like to be able to copy and paste the contents of (“b†& target row)
to Sheet2 in the same Workbook and still have the User Form (CriteriaReached)
show.

Please will someone show me how to do this.

Thank you.

 

[Bernie Deitrick]

Rob,

Where in sheet2? I have assumed that you want the copied cell or value in
the first blank cell at the bottom of column B.

If ActiveCell<ActiveCell.Offset(0,2) And ActiveCell.Offset(0,2) < _
ActiveCell.Offset(0, 4) Then
'Use ONE of these lines
'To copy the cell
' Cells(ActiveCell.Row,2).Copy
Sheets("Sheet2").Cells(Rows.Count,2).End(xlUp)(2)
'To put the value into another cell
' Sheets("Sheet2").Cells(Rows.Count,2).End(xlUp)(2).Value =
Cells(ActiveCell.Row,2).Value
CriteriaReached.Show
ActiveCell.Offset(1, 0).Activate
Else
......

HTH,
Bernie
MS Excel MVP