Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm new to macros and hope somebody can help Trying to make a report from a datasheet that meets some conditions. I need to fetch all the data (cells) in a row e.g. that meet the condition <1 in column 10 and copy those cells/row to a new sheet. I cant find any functions i Excel so I hope a not to advanced macro which I can modify can do the trick. Thanks in advance.

share|improve this question
  • Open a copy of your excel sheet containing all the data
  • press ALT+F11 to open the VBA editor
  • open sheet1 from the left pane and insert the code below

    Sub mymacro()
      For myrow = 1 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If Cells(myrow, 8) < 1 Then
          i = i + 1
          ActiveWorkbook.Sheets(1).Rows(myrow).Copy Destination:=ActiveWorkbook.Sheets(2).Rows(i)
        End If
      Next myrow
    End Sub
  • go back to your sheet and press ALT+F8 and execute your macro

This code will run until the last used row, check if the value of 8th column is < 1 and if yes, copy the whole row to the next free row in sheet 2.

share|improve this answer
ok, thanks. I will try. Its a lot of rows with cars we have sold. In column "H" I add a "1" if the car is delivered. In every end of month I have to send a orderreserve and I then want to just fetch all the rows with cars that is not delivered and make a report to send. It's maybe a cumbersome way to do it, but i'm a novice :-) – Frank Sannes Jan 2 '13 at 21:43
marvellous :-) If I can bother you more I'm looking for the data who occurs in row 14 to 1000. maybe it will be quicker when the array is defined. Ok, I promise that I will try harder to learn macro programming my self :-) – Frank Sannes Jan 2 '13 at 22:50
But I want you to learn something :) What you want is really very simple. I give you a hint and you try it, ok? You have to edit line 2. – nixda Jan 2 '13 at 22:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .