VBA to use Autofilter to move data from one sheet to another

[mrzesty]

Hi,

I am trying to write vba code to take data from one sheet and move it to a new sheet based on the condition of paid. The raw data are in columns A to F on the sheet called AR.

I try to move them to the sheet called Paid, based on a msg box input where the user types in the word Paid. The code works on a small data set, so it takes the data and moves it onto the paid sheet.

But when I try it on large sets(over 600 records), it keeps repeating the output. So I see columns A to F with the proper data. Then starting in columns G and going all the way out to the last possible column, the data is repeated.

I have uploaded a file that shows the code.

Thanks you

 

[Hui]

You may wish to see how it was tackled in these two examples

Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/

Advanced Filter Move Data to other Files
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

 

[Monty]

Hello mrzesty

Try this.


Let me know any challenges..Happy to help you.

 

[mrzesty]

Hello Monty,

It worked!!

Cool!! Cool!!

Thanks

 

[Monty]

Hello mrzesty

You are welcome..it took 3 hours to do your job...Iam happy that you are happy...Please let me know if you have any challenges....Good luck.

 

[mrzesty]

Hi Monty,

Sorry for the delay in reply back. I've been testing the code out over this past week and its helped me sort the data as needed. Thanks for taking the time to write the code. What were some of the issues that came into play which extended the development time? I was banging my head too.

Would you be willing to tackle the next phase to the project?

What I mean is that the invoices can have three stages of payment. The payment stages are Paid, Approved, Waiting.

Each stage is logged into the file, so that we can track its progress through our payment system. That means the starting data set will show a repeated invoice number, because it had a different payment stage. I've uploaded a new file.

After the invoices have been sorted onto the different sheets, what I need the updated code to do is to look across the sheets, and keep the invoices that are paid; and remove the invoices that have the earlier stage of approved and waiting. If the invoice has not reached the Paid stage but made it to the Approved stage, then I need the code to delete the Waiting stage.

I believe a CASE statement can address this, but I don't know how to script one for looking across the worksheets.

Thank you
MrZ.