VBA - Custom Filter

I have a bit of a weird one - -- --- -- -- --

I have a huge spreadsheet with a list of Pay Periods in Column A (e.g. 200834) and Employee IDs in Column B (e.g. 3007).

Columns C - ZZ contain figures, which are grouped into 3-5 column-wide blocks or related data. For example Columns E-G all contain tax data for a specific Employee in that specific Pay Period.

What I want (need really) is a filter that lets me input a period number and a range of columns.

The resulting columns (e.g. A-B AND E-G) then need to be copied to a new sheet.

It would be amazing if I could also specify a sheet name in the filter.

There's one other complication.

The values on the sheet (which is sheet 1 of the WB) are generated via formulas on sheet 2 of the same WB.

So, if I select a value on sheet two I actually see something like "='SETUP-TRESHOLDS-VARIABLES'!$K$8" in the Formula Bar; the cell however contains the resulting data (in that case 500.00).

Any ideas?

I'm in 2007 if that matters. (I've seen contradictory thoughts on that, btw.)

Thanks so much guys and gals and help is mucho appreciado.

--

BTW> I have tried doing this about 10 different ways, cannibalizing other script, etc... but due to the dynamic nature of the data, nothing has worked...

thanks for the response...

we're parsing out the bits to different people who "don't need" to have access to the rest of the data and we're also stripping out the results into separate csv files for import...

does that make sense?

thanks!!!

wow! I'm amazed... I just tried a good excel programmer I know and they told me they couldn't do it...

is this really that difficult?

should I try and get a developer here to spend some time on it?

shows what I know :P

still no answer?

Originally Posted by royUK

It's difficult to help without seeing an example of the layout

D'oh!

thanks Roy!!!

I have attached the spreadsheet..


does that help ?



I really wasn't trying to be a jerk, just trying to figure out if it's something I need to spend developer resources on, etc.

:P
----------------
Now playing: Dead Letters Spell Out Dead Words / This Room Seems Empty Without You

Originally Posted by royUK

It's still not very clear, do you want to filter the period & copy the data for that period?

I filter for 200804

Col B contains data P, Q & R

Sorry Roy, you're a saint for being so patient.

Yes, I want to be able to say 200804, Rows A-B, and J-L (for example).

This would take all rows with the date 200804 (so like 100 rows) and copy and paste the data from those specific columns into a new workbook... or even better a new .csv document... but a new workbook/sheet is totally cool...

heck, even just a new worksheet in the same workbook is super cool... I just have to do this all the time and it's so incredibly tedious + I have to show a few other this ridiculous task... and they always screw it up...

so if I could make this easier for all of us (and produce a more trustworthy result) I would be so relived...

thanks again Roy!!



----------------
Now playing: Modest Mouse / Third Planet (Demo)

same format please ... sometimes those column DO have data.... pain huh?

Originally Posted by royUK

I'm not sure how you would know which columns you need.

I need to be able to enter them at runtime... otherwise I'd need several versions...

For instance... one is A-J, one is A-B + K-M, one is A-B + N-O, etc. etc.

("-" means through not minus, btw.)

It would be cool if there was a way to make presets etc., but there probably isn't... so if I could just say (for example):

year = 200805
Columns = a-b, n-o
name = xrima tpl.csv

and it would generate a .csv called "xrima tpl" that had every row that had 200805 in Column A with the data from columns a, b, n, m and o.

Does that make sense?

So I could choose this stuff at run time every time...

That's what makes this all so tricky... the super-advanced bit

Thanks for soldiering on Roy... I really genuinely appreciate it.