How to copy entire row based on criteria

[ckreilick]

I cannot seem to figure out macros. I have been doing research on this for hours now, and I have tried using others post and modifying their code, but each time it comes back with an error. I have a spreadsheet that keeps track of all of the invoices I issue for my company, and than I track the invoices on whether they are 30 days, 60 days, or 90 days overdue. I have a column for 30 days, 60 days, and 90 days, I update my columns by placing a "Not Paid" in the column when the invoice passes the 30 day overdue mark. And so on and so forth for 60 days and 90 days overdue, but once the invoice is paid I insert Paid into the correct column and row. What I would like is to assign a macro that checks the first tab called "Invoice Information". And if for say under column H or "30 days" invoice 1 says not paid it copies that entire row and puts it on tab "30 Days". I also want the same to happen for column I for 60 days to go to tab "60 Days" and column J for 90 days to copy "90 days tab" Can someone please help me.

[daffodil11]

Sure. I've taught myself enough Visual Basic in the last few months to get this kind of thing done.
And since I'm still mostly a VB noob, I can describe in a way average people might understand, mostly because of my lack of technical understanding of it. Kind of like knowing how to fix a car, without knowing how a car works.

First, I'd recommend you post a sample of your work. If the data is confidential or classified, then desensitize. Scrub the stuff off of it you can't share. What I need are the actual rows, columns of what we'll move, measure, compare.

You can attach a workbook by clicking on Go Advanced, then scrolling down to Manage Attachments, and then browsing to your file.

Your first line of the macro is the name of the subroutine. Sub MoveStuff() works fine. Some macros have extra parameters in the (); I doubt this one needs it.

Next you declare your variables, which you can name practically anything. Some programmers label them after their function, some name them something weird.
There's a lot of types of variables, such as Range, Integer, Long, Double. It defines how the variable will be used. We'll use Range. They are defined with Dim meaning dimension. I'm not a programmer, so I don't understand a lot of why.

Dim cells as Range

We are declaring the word cells as a variable, and it's a range.

[Telperion]

This sounds like a job for pivot tables.

One column called "Status" which could have values "Overdue 30", "Overdue 60", "Overdue 90", "Paid", and "Outstanding". Pick whatever values you want. Then just use a pivot table to report on the actual values.

[daffodil11]

We'll be doing something over and over, so we can use things like Do While or For which both can repeat.

For example:

Please Login or Register  to view this content.

This will literally make A1 = 1, then 2, then 3 as it loops over and over each time adding 1 to itself. When x is no longer <10000 the loop will stop.

For you, I'd go with For Each which does a similar kind of thing.

Please Login or Register  to view this content.

This looks at every cell in H2:H500 one at a time, checks if it is greater than 30, and if it is it copies the whole row to another sheet which I'm calling 30 Days Overdue, into the next empty row under column A. Then it repeats, over and over until it has checked all of those cells.


Do While -> Loop and For -> Next are both really good ways to get your feet wet in basic coding. Once you can move data around a little bit, you can start to find more and more elaborate and complicated ways.

[ckreilick]

Here's a copy of the workbook.

[daffodil11]

How about something like this?

Please Login or Register  to view this content.

[ckreilick]

That works perfect! I couldn't thank you enough!