Hi,

It's a bit messy because of the order you want the data transferring but
this seems to work. Right click the sheet tab of the sheet with your data in,
view code and paste this in and data are copied to sheet 2.

Sub copyit()
Dim MyRange As Range
Set MyRange = Range("B2:B500")
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For Each c In MyRange
If InStr(1, UCase(c.Value), "EMERGENCY", 0) Then
Sheets("Sheet2").Cells(lastrow, 1).Value = c.Offset(, 6).Value
Sheets("Sheet2").Cells(lastrow, 2).Value = c.Offset(, 7).Value
Sheets("Sheet2").Cells(lastrow, 3).Value = c.Offset(, 8).Value
Sheets("Sheet2").Cells(lastrow, 4).Value = c.Offset(, 1).Value
Sheets("Sheet2").Cells(lastrow, 5).Value = c.Offset(, 2).Value
Sheets("Sheet2").Cells(lastrow, 6).Value = c.Offset(, 3).Value
Sheets("Sheet2").Cells(lastrow, 7).Value = c.Offset(, 4).Value
Sheets("Sheet2").Cells(lastrow, 8).Value = c.Offset(, 5).Value
lastrow = lastrow + 1
End If
Next
End Sub

Mike

"Rodman" wrote:

I am trying to copy a range of cells from one worksheet to another
(worksheet); based on the text in a specific cell.

Using the range B2 through H500:
If the text in cell B(x) = "Emergency"
then copy cells H(x), I(x), J(x), C(x), D(x), E(x), F(x), G(x)
into another worksheet in the same order.

(In the above example x represents the range 2 through 500)

I have tried using a formula but that doen not work. Any suggestions in the
form of a formula of a Macro/VBA would be greately appreciated!

Can Anyone Help?

Thanks in Advance.

--
Rodman Veney