Copy - Paste Special Filtered Data

**longbow007** · 06-12-2010, 05:29 AM

Hello, could some one please help me with the following:

I have started a macro in Excel 2003 that asks the user for today's date and filters worksheet named E-1. In column AN it filters all dates that are less than or equal to today's date and in column AQ it filters all cells with a value equal to FILLED.

What I need:

For each worksheet: E-1, E-2, E-3, E-4, E-5, E-7, E-9, E-10, E-11, E-12, E-15, E-17, E-18 and E-19 the following tasks need to be completed on the filtered data:

1. the filtered data range: AQ:BM needs to be copied;

2. It needs to be pasted special (values) to filtered range: T:AP;

3. the filtered data in column A needs to be copied and pasted to worksheet named: Replaced Positions in column A.

4. the filtered data in range AQ:BM needs to be cleared;

5. the filtered cells in column AQ need their cell values changed to: VACANT.

Please note the following:

I have attached in the .zip file that contains some screen-shots as follows:

1. Before Current Incumbent Data with filter applied.png shows an example of the filter being applied to columns AN and AQ.

2. Before Replacement Data with filter appied.png shows the same example as details above, however i was unable to capture the entire width of the screen-shot in one-picture due to the number of columns involved in the worksheet.

3. After Current Incumbent Data with paste special.png shows where the copied filtered data from range: AQ:BM is pasted special (values) to range T:AP.

4. After Replacement Data with data cleared and column AQ reset to VACANT.png shows that after the paste special (values), the filtered data in range AQ:BM is cleared and then the filtered cells in column AQ have their values set to: VACANT.

5. I have only included some of the worksheets in the attached workbook to save on file space.

If any one could please help, it would be greatly appreciated.

Kind regards,

Chris

Please Login or Register  to view this content.

**pike** · 06-12-2010, 07:25 PM

Hi longbow007,
possibly best if you break your requests down to one question a thread and work throught the problems. 340k zip file is huge

**longbow007** · 06-12-2010, 08:15 PM

Hello, I just wanted to clarify what I am trying to do:

When the user runs the macro, it automatically selects worksheet named: E-1 and the user is prompted by an InputBox to enter today's date. The macro then automatically filters all those dates that are less than or equal to the date entered in column AN. It also automatically filters all those values in column AQ that are equal to: FILLED.

Procedure:

With the filters applied, if there is any data showing then I need the macro to automatically select the filtered data range AQ:BM and copy it to the filtered range T:AP and paste it (Paste Special - Values). Afterwhich, It
needs to clear the data it copied from range: AQ:BM (so that all the data in filtered range: AQ:BM is clear). The filtered cells in column AQ need to populated with the value: VACANT. Please refer to the attached .zip file for screen-shots. Finally, the filtered Position Numbers in column A needs to be copied to column A of worksheet named: Replaced Positions.

End procedure

This procedure needs to loop around for all worksheet names starting with the letter E*

I hope this helps, if not please let me know.

Cheers,

Chris

**longbow007** · 06-12-2010, 08:24 PM

Hi pike, sorry about the large file size, the actual spreadsheet is large because each worksheet contained within the spreadsheet is exactly the same (that is all worksheets that have a name prefixed with the letter E). I purposely left out a number of these worksheets to keep the size down. However, I need to keep several of them so that any one that is able to help me could use the attached spreadsheet to test out any possible solutions.

The actual problem is being able to copy and paste special filtered data which unfortunately I do not know how to do with VBA.

I have tried clarifying my request so that hopefully it makes some sort of sense to some one.

Sometimes it is difficult to post problems which are hard to articulate in words.

If you are able to help, it would be greatly appreciated.

Kind regards,

Chris

**pike** · 06-12-2010, 10:05 PM

Hi longbow007,

Till I get a chance to down load the file try, recording a macro of the steps you wish to perform with the filter ect... It a great way to see what has to be done. The we can add code to adapted for input boxes ect..

**longbow007** · 06-12-2010, 10:46 PM

Hi pike,

Please see the macro recorder code as requested:

Please Login or Register  to view this content.

I hope this helps

Cheers,

Chris

**pike** · 06-13-2010, 05:33 AM

not quite what I had in mind, but a good start.
Recording the filter ranges and copying gives me

Please Login or Register  to view this content.

Recording macro is the best way to start understanding the code

**longbow007** · 06-13-2010, 06:40 PM

Hi pike, thanks for your reply. Unfortunately after testing your code, it does not do what I need it to do. I was wondering if you could please so kind as to open my workbook, select worksheet named E-1 and the apply a custom filter (via AutoFilter) in column AN and select all those dates that are less than or equal to 12/06/10 (dd/mm/yy) format - sorry I live in Australia.. Then, apply another custom filter to column AQ for all those values equal to FILLED.

Then, run the following updated recorded macro to see what I am trying to achieve.

Kind regards,

Chris

Please Login or Register  to view this content.

**pike** · 06-15-2010, 02:31 AM

hi longbow007
I can see that your problem is copying from a multi filtered range to multi filltered range

try

Please Login or Register  to view this content.

in xls 2007 on the ribbon under "find select" there is "special goto" "Visible cells" do you have this option?

**longbow007** · 06-15-2010, 06:17 AM

Hi pike, thanks for all your help - much appreciated

However, your code does not seem to work the way I expected.

I edited the code as shown below and after running it, I could see no difference in copying from a multi filtered range to multi filltered range.

The other minor problem is that the copy range will vary. It will not (in this case be always in the range AQ101:BM141.

Are you able to please further assist?

Kind regards,

Chris

Please Login or Register  to view this content.

**longbow007** · 06-15-2010, 06:19 AM

Hi pike, oops forgot to mention that I am using Excel 2003.

[wrap]in xls 2007 on the ribbon under "find select" there is "special goto" "Visible cells" do you have this option?[/wrap]

So, sorry I do not have this option.

Cheers,

Chris

**pike** · 06-15-2010, 06:29 AM

Yep, "find select" there is "special goto" "Visible cells" is in 2007xl

As for Copying some columns from a filtered range and then pasting back to different columns in the same filtered range. no goood sofar.But I havent given up yet + someone else have a solution

**pike** · 06-15-2010, 06:54 AM

Hi longbow007
This is the work around
http://www.handyexceltips.com/2008/1...filtered-data/
It can be programmed but it may be better to redesign the spread sheet?

**longbow007** · 06-15-2010, 07:55 AM

Hi pike, unfortunately, the original spreadsheet (not the dummy data one) is not my spreadsheet. It can not be re-designed. It is very poorly designed. I just have to live with it until one day we replace it with possibly an MS Access database or an SQL database.

The spreadsheet is an Excel 2003 spreadsheet.

I hope a solution can be found and I do really appreciate all your hard efforts.

Kind regards,

Chris

**pike** · 06-15-2010, 08:41 AM

the next step is to loop through the ranges find "fill" with the correct date
cut 23 columns in row and then paste 23 row to left

**longbow007** · 06-15-2010, 05:44 PM

Hi pike,

the next step is to loop through the ranges find "fill" with the correct date
cut 23 columns in row and then paste 23 row to left

I am a newbie to VBA programming and have no idea how to do what you are suggesting.

If you would be so kind as to further help with your idea, that would be really great.

Cheers,

Chris

**pike** · 06-24-2010, 07:40 AM

hey longbow007
what happen to kevin 07
any who I was thinking algong these lines

Please Login or Register  to view this content.

**longbow007** · 06-26-2010, 06:22 AM

Hi pike, thanks so much for your excellent code - I am currently adapting it to suit my macro. I have run it and it works well. Really appreciate all your help and assistance.

Cheers,

Chris

Copy - Paste Special Filtered Data

LinkBack

Thread Tools

Rate This Thread

Display

Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Re: Copy - Paste Special Filtered Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions