Filter, copy specific columns, and paste to another sheet.

Good morning,
Yeah it is 1:33 am where I am.
I'd like to filter a column and paste only 2 columns to another sheet in the first available row.
I found a code that was posted by Tom Ogilvy and made a few adjustments:

Please Login or Register  to view this content.

I can't seem to adjust it to copy 2 columns.
I've attached a file.
Can anyone help?
Regards,
Xrull

If the 2 columns are contiguous try altering the Resize from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

EDIT: just looked at the attachment and note the above will not always be appropriate (as is)

Questions...

Are you only ever copying 2 columns or is the intention such that you will modify headers on Sheet2 row 1 and you intend to copy data from Sheet1 to match the headers listed on Sheet2 (B1 onwards) ?

Slipped under the radar... sorry... perhaps:

Please Login or Register  to view this content.

Alter sheets & ranges to suit requirements.

It is me again.
DonkeyOte, I ran the code, but I keep getting the message:

PHP Code: 

Run-time error '91':
Object Variable or With block variable not set
The error is on this line: 


Please Login or Register  to view this content.

Thanks again,
Xrull

Can you create a file which replicates your setup and has the code in place (which generates the error) ?

Here is a working example:

Assuming CO31 is not hidden as a result of the filtering process perhaps add:

Please Login or Register  to view this content.

on the line preceding the IF block.

Is "b" a named range ?

Yes: Then I presume it's on a sheet away from that being filtered in which case remove the preceding period (.) from before the Range reference

No: Which cell contains the filter criteria ? Or is "b" meant to be a constant - ie filter "b" ?

"b" is not a Range object so remove that highlighted in red:

Please Login or Register  to view this content.

I confess I'm getting confused - before you wanted to use a variable whereas now you're using a constant... why not post a file that reflects your real setup and outline what you want to do exactly.... do you really intend to use a constant in the filter ?
As is this thread is getting a little out of hand for what should in reality be a pretty straightforward exercise.

This worked in your file (for me) without incident... this is exactly the same code as before with exception of reset of filter on BB which was not a requirement previously.

Please Login or Register  to view this content.

Re: post 16 and your error, results from:

Please Login or Register  to view this content.

In the above you are applying the AutoFilter to a range which has yet to be Set (rngData) - ie the lines should be swapped around in their order per the prior code.