Hi,

I was wondering if anyone can help me I am a fairly new user to VB excel and I hav designed a stock control sheet where I have ran some macros, so that when a assembled productr is sent out a macro removes the items from the stock. I have also put in a shedule so I know how much stock is required for the next 3 months

e.g. column n - total sock column o-stock after month 1

what need is some coding that searches the sheet for the minus numbers in the stock column and copys them to a new sheet can anyone help me

thanks :)

Regards,
Kamal

Hi,
 Sorry about the previous post. The following sample code might help you. You can specify the range and destination sheet according
 to your needs.

  Dim cell As Range
  Dim I As Integer,sourceCol As Integer, sourceStartRow As Integer, sourceEndRow As Integer
  Dim destSheetIndex
  sourceCol = 2
  sourceStartRow = 5
  sourceEndRow = 10
  destSheetIndex = 2
  For Each cell In Range(Cells(sourceStartRow, sourceCol), Cells(sourceEndRow, sourceCol))
        If cell.Value < 0 Then
            I = I + 1
            cell.Copy Destination:=Worksheets(destSheetIndex).Cells(I, 1)
        End If
  Next

 If you are using the named range, then you can specify something like this

 "For Each cell In Range("myRange").Columns(colToSearch)" instead of

  "For Each cell In Range(Cells(sourceStartRow, sourceCol), Cells(sourceEndRow, sourceCol))"


Regards,
Kamal