VBA - Custom Filter

**leelikchi** · 11-25-2008, 08:07 AM

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...

**royUK** · 11-25-2008, 08:17 AM

Why don't you just keep all the data on one sheet but use Custom Views to see relevant data?

**leelikchi** · 11-25-2008, 08:51 AM

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!!!

**royUK** · 11-25-2008, 09:10 AM

It would still be easier to use one sheet for data

**leelikchi** · 11-25-2008, 09:12 AM

will that make it impossible?

we need to be able to globally update some of that data on the other sheet...so that's why... I suppose I could always just save the data sheet independently.. if that would help, consider it done!!!

whatever it takes...

--

EDIT:

I have created a single sheet version... can I post that here somehow

**leelikchi** · 11-25-2008, 11:52 AM

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

**leelikchi** · 12-01-2008, 06:58 AM

still no answer?

**royUK** · 12-01-2008, 07:10 AM

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

**leelikchi** · 12-01-2008, 07:38 AM

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

**royUK** · 12-01-2008, 07:52 AM

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

**leelikchi** · 12-01-2008, 09:58 AM

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)

**royUK** · 12-01-2008, 10:25 AM

Do you need to keep the same format, with empty columns or just the columns containing data?

**leelikchi** · 12-01-2008, 10:44 AM

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

**royUK** · 12-01-2008, 11:00 AM

Try this

Option Explicit

Sub FilterToCSV()
'
    Dim ans    As String
    Dim rCopy  As Range
    With Application
        .ScreenUpdating = False
        ans = InputBox("Enter date required. Format DDMMYY")
        If ans = "" Then Exit Sub
        ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=ans
        Set rCopy = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
        Worksheets.Add
        ActiveSheet.Name = ans
        rCopy.Copy Sheets(ans).Cells(1, 1)
        ActiveSheet.Copy
        'change the directory to save to
        ActiveWorkbook.SaveAs Filename:="C:\" & ans & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close True
        .DisplayAlerts = False
        Sheets(ans).Delete
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

**leelikchi** · 12-01-2008, 11:01 AM

Originally Posted by royUK

Try this

Option Explicit

Sub FilterToCSV()
'
    Dim ans    As String
    Dim rCopy  As Range
    With Application
        .ScreenUpdating = False
        ans = InputBox("Enter date required. Format DDMMYY")
        If ans = "" Then Exit Sub
        ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=ans
        Set rCopy = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
        Worksheets.Add
        ActiveSheet.Name = ans
        rCopy.Copy Sheets(ans).Cells(1, 1)
        ActiveSheet.Copy
        'change the directory to save to
        ActiveWorkbook.SaveAs Filename:="C:\" & ans & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close True
        .DisplayAlerts = False
        Sheets(ans).Delete
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

trying now....

ok...

well..

that didnt' seem to work.

it definitely does do half of it, but I probably (as usual) wasn't explicit...

--

In other words

--

Can I choose which columns I want to copy onto the new worksheet at runtime?

And...

Can I use the yyyymm format instead of the ddmmyy format for dates (just to mimic the existing date format)?

Aren't I a jerk

Thread: VBA - Custom Filter

Thread Tools

Display

VBA - Custom Filter

because

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions