Ok I have 2 different work books...1: Post Date Log...2: Promise to Pay Log...
What I have in the promise to pay log is a table that has headers of:
•account
•date
•payment date
•customer name
•balance
•payment
•payment method
•status
•phone
•paid?
the post date log table has the following table headers.
•account
•date
•payment date
•customer name
•amount
•payment type (different then payment method)
What I want it to do is check each row to see if paid? has a Y in it then in needs to copy
•account
•date
•payment date
•customer name
•payment
•payment method
to the post date log table....
I want it to paste it in the exact same order... Is this possible with two work books or would it be faster and more logical to do it in one workbook with seperate worksheets....
also to throw a kink into thinks I need it to put it into the right month based on the payment date... in post date log i have the work sheets:
•JUN 11
•JUL 11
•AUG 11
•SEP 11
•OCT 11
•NOV 11
•DEC 11
each worksheet representing its corresponding month...
any help is greatly appriciated...Microsoft Office 2007 if that is needed also
Update:well i have like zero experience with macros and truly don't even know how to open the macro sheet to be able to record how would I do it with filters as I do understand those some what
Answers & Comments
Verified answer
use a for loop that goes from row 1 to the max rows.
{
check to see if paid column value has 'y'
if yes copy over to the free row in the paid sheet (and increase the free row variable)
}
most of the code can be obtained by pressing the record button and doing a single copy yourself. you will need to add the loop and if statement.
Alternatively if you don't like macros, you can use autofilters to do this.
edit:
to use the filters press ctrl+a to select all cells on the pay log. on the ribbon click data-> filter
click the down arrow on the paid? column header and select only the y and click ok
now the rows that are paid are shown, almost there.
right click on the column letters of the column you don't wish to copy and choose hide.
ctrl +a to select all cells. ctrl + c to copy, switch to the post sheet and ctrl +v to paste. It should only paste visible cells so you only have the data you want.
I don't write VB scripts often, so I usually use the "record macro" feature to get a start on the code.
However it seems straightforward.It could help if the paid? column had either Y or N. Then when you encounter an empty cell, you know you've run out of data.
You could do it manually in this manner. First add a column to retain the original order of the rows. Call it whatever you want (I usually use "seq" for sequence). Put a 1 in the first cell, then just drag it down to the last row, and I think you have to use the CTRL key to get a sequence otherwise you'll get all 1's. In the copied workbook, sort by the paid? column and delete all rows without a Y. Then sort by the seq column.
Adding a "month" column to the original data where each cell uses the MONTH() function on the date, would help so you could sort by month (and secondarily on seq).
But frankly, what's wrong with just having one sheet. A seq column can be used to remember the original order if that's really important (maybe the date would be sufficient). You could then sort and resort according to the needs of the moment.
If you have no programming experience, then developing a macro will prove difficult for you.