Macro to search column and paste row

[sieira67]

So I've been trying to put together a code in VBA that will search column A from A1 to A500 for different words (i.e. Mason, Electric, HVAC) and then copy and paste that row in a desired location on a different sheet. The way I've been doing it is much to complicated and I KNOW there is an easier way to do this.

Figuratively, I would like it to run like this

Range A1:A500

If A1 is Mason then copy & paste row into row 2 of sheet 2.
Else If A1 is Electric then copy & paste row into row 5 of sheet 2
Else if A1 is HVAC then copy & paste row into row 10 of sheet 2
,,,, and so on for the other words I need filtered.

I also need the program to know to move down a space on the second sheet so that there it wont overlap with the other pasted rows.

Thanks for the help

[Deamo]

could something like this work:

Please Login or Register  to view this content.

Please forgive any errors.. very quick and dirty attempt with zero testing.

[davesexcel]

This example will loop through the items and paste the found rows into sheet 2.
The list of items to find is in sheet3 starting at A10, it will copy and paste the rows from sheet 1 to sheet 2 in the order of your listed items

[sieira67]

First, thanks deamo and dave for taking the time to help out.

Dave, your code works nearly perfectly. The only thing I need is some sort of gap between each filter. I tried some things myself to make this happen but I was unsuccessful.

How about if I put the paste row next to the search word in sheet3?

[sieira67]

I tried inserting an offset before the each of the next commands at the end of daves code, but it hasn't worked. Any other suggestions?

[davesexcel]

This may be what you are looking for

Please Login or Register  to view this content.

[sieira67]

That is perfect. Your the man Dave! The program does exactly what I needed.

I realize there is something else I would like to add to the code to make it a little more convenient for me. I was hoping that under each of the separated sections in the second sheet, I could sum the values of column B. Im going to give the code a shot myself, but I would appreciate any help.

Thanks

[sieira67]

I have been searching the web and trying to figure out a solution to this. I wrote a code that inserts sumif formulas into desired cells next to the copied rows on the second sheet, but I cant figure out whats wrong with my syntax. I tried the following line but the quotations in the criteria part of my formula keep on making an error message appear. Im not sure how to handle this or if there is a better way. I would really appreciate any help.

Please Login or Register  to view this content.

[davesexcel]

If "Site Work" is what you are looking for, Double Quotes may be required ""Site Work""

If you start a line with an open Quote the next quote will be considered a closing quote in VBA. That's why you would need a double quote.

[sieira67]

Your the man dave