VBA to auto filter to separate worksheets?

I have data that I need to separate out into five different sheets.

Attached is the workbook.

Source Sheet is blank, that is where we will copy/paste the raw data too.
From there we will filter out everything but the cells that begin in "FA"
All the "FA" cells are then copied to the PAY FILE, starting at A2
All that is then filtered already on the FILTER worksheet. (currently the formulas in FILTER stops at 200, but that was due to size constraints, original file has it down to A75000)
Then we filter those and move all the "RH" "VO" "WG" "NSG" "CN"

For example, I have one row already copied over, you see in Column F, Reason, it has a WG, and there is a corresponding worksheet at the bottom. That would copy from the FILTER page to the WG page.

Then I have on the Grand Total an autosum formula already in place that pulls the AMT from each worksheet.

Hopefully that was a decent explanation, but our resident programmer up and quit on us after I spent 8 hours with her yesterday trying to create outlines of how she runs her reports because she had given notice and was helping us be able to transition. So we are in a rock and a hard place.

She did this manually every month, but I think with the freaking amazing help with macro's i have already received, that maybe I can get that again.

Hey syss, yes I apologize about my misspellings, I submitted while still at work and dealing with the fallout lol. Yes NSF. And yes, some of the values in the reason are for whatever reason not important for our purposes.

And I think I agree and posted it above too that formulas would probably be the better way. I can manage formulas ok, but VBA or anything else is really beyond me and I don't want to have to be the point of contact for this thing forever. I'm just doing this now because we are between a rock and hard place and I want to make sure the task gets done correctly.

edit**

Hey Syss, I just ran yours on a file that was sent out yesterday and it didn't copy the files over to the right reasons. It did copy from the source file to the pay file the same number of rows. So that was perfect, but it didn't everything over.

I attached a sheet that has six examples. Three "RH" and three "VO", so maybe you can see if something is wrong.

Man, idk, I literally downloaded, opened, hit clear contents, then hit run filter, and it moves everything to the pay file, but either way. I closed out without saving, re-opened and went straight to the grand total and other tabs. All blank. Sorry!

Edit* WTF, I just reopened your file and ran it and it worked on that data. I am going to try some other data and see!!

Hey jomili, Thanks! A few issues I think though. If you take the below data and put into your sheet, it says the SSN is 945632187, but should be 367894512, (comes after the S in the data).

FAG605S3678945120000102297491120160819201608190000000498372RHP20160820999999WO000231090727AG605PC

Also, i'm not seeing what I originally was hoping for, maybe I am missing it. But it seems that you still have to manually filter the Source sheet and copy that data over to the Filter sheet? It looks like you could even reduce yours to one page right? basically copy that yellow table over to the source file. Because the filter sheet doesn't do anything that the formulas on Source don't do as well right?

What my goal and hope is, that I can get this to be basically fool proof. That all someone has to do is copy and paste that data into one column on the source sheet and then if it starts with FA*, then to look at that reason code and copy over to the corresponding worksheet and then sum those amounts.

Copy/Paste into Source>IF begins FA* look at (RH,VO,WG,NSF,CN) and copy to worksheet with same name then autosum.

*edit, Hey i was looking at it again and my column G has no formula, it just has WG in it. I know it is something like..

Formula:

Please Login or Register  to view this content.

but I think maybe some of my data has one character short because of me anonymizing it

Ah, nevermind. Disregard this post. Idk how to delete though.

Originally Posted by taylorsm

Man, idk, I literally downloaded, opened, hit clear contents, then hit run filter, and it moves everything to the pay file, but either way. I closed out without saving, re-opened and went straight to the grand total and other tabs. All blank. Sorry!

Edit* WTF, I just reopened your file and ran it and it worked on that data. I am going to try some other data and see!!

Hello taylorsm,

the button Clear contents is there for cleaning the filter off (remove data from helper sheets). I tested again and works well at my side. Of course I don't know if that total is correct or not.

I uploaded a ver2 with only one change: I moved buttons to the last sheet. It is more obvious here that something happened after button is pressed.

This is what I get when I hit Run Filter:
GrandTotal.PNG

And this is how sheet VO looks like:
VO.PNG

Hope this will help you

sysss