Problems copying filtered data

HIV 7 · #1 09-05-2018, 01:29 AM

Hi experts,

i just watched some videos and read some site about my topic, but I really have not found a solution for what I thought it has been working in the past ....

I Atached two Files as an example of what I mean (one Word containig Screenshots and the Excel File I used), I have 10 rows and two columns. In columns one is M or W and in Column 2 there is a number.
What I need to do is to copy the data of Column 2 in Column 5 (as my excel is an example), but I have an active filtering in COlumn 1 on the criteria M.

Can someone help me to get this done? I tried only copy vsible cells/rows, but that does not bring me up to what I need.

Would be so great to hear from you experts if there is a solution for this topic.

Kr, HIV 7

p45cal · #2 09-05-2018, 03:09 PM

Code:

Sub blah()
ActiveSheet.Range("B1").CurrentRegion.AutoFilter 1, "M"
ActiveSheet.AutoFilter.Range.Offset(1).Columns(2).Copy Range("E2")
ActiveSheet.ShowAllData
End Sub

?

NoSparks · #3 09-05-2018, 04:40 PM

No code wanted.

HIV 7 · #4 09-05-2018, 10:55 PM

Hi p45cal,

thanks für the quick answer. With "Coding" I know the solution, but my boss wants to have a solution without any code/script.

So the question is, if this is possible to be done using "board/standard functions" from excel?

Kr,
HIV 7

ArviLaanemets · #5 09-05-2018, 11:12 PM

Paste ignores cells hidden with autofilter. Btw, it is very bad practice to have several different tables on same sheet, especially when those tables share same rows. Paste to another sheet, or at least into range below original datarange.

HIV 7 · #6 09-05-2018, 11:13 PM

Hi ArviLaanemets,

so it is not possible what I need? And I know what you mean.

Kr,
HIV 7

ArviLaanemets · #7 09-05-2018, 11:22 PM

It is possible with formulas, or when you copy data, remove autofilter, and then paste, but I strongly advice against this in way how you want it. The user will see copied data correctly only then when there is no autofilter applied.

With formulas you can have a report on separate sheet. You select the "M" or "W" from data value list in some cell (at top of report) on report sheet, and according rows from data sheet are displayed. You can get an idea how this can be done from another response I wrote to another thread in same forum today (entries on your data sheet must be enumerated according the selection on report sheet).

p45cal · #8 09-06-2018, 03:26 AM

You can try the following, but be careful to follow instructions carefully. Using your sample file:
1. Filter the range for M
2. We are going to place the results in column F, so select cells F2 down to level with the bottom of the table. (in this case F2:F9, even though some cells are invisible).
3. With those cells still selected, on the keyboard, type the = sign, then using the left arrow cursor key on the keyboard, press it until cell C2 is selected. Do not use the mouse for anything at this stage.
4. Hold down the Ctrl key on the keyboard and press Enter on the keyboard. This will put a formula in all the selected (but only the visible) cells.
5. Clear the filter from the filtered range.
6. Select all the cells from F2:F11 as a single block (actually you need only select all cells with formulae in, but it must still be as a single block) then…
7. Copy|Paste Special, Values.
QED

p45cal · #9 09-24-2018, 10:34 AM

Quote:

Originally Posted by p45cal

You can try the following, but

I wonder whether HIV 7 has his problem solved? …

p45cal · #10 09-28-2018, 02:56 AM

Oh well, can you guess who I won't bother to try and help again?

NoSparks · #11 09-28-2018, 05:43 AM

@ p45cal

Based on the OP's last log-in, it appears they were in discussion with ArviLaanemets for about 25 minutes with posts 4 thru 7.
Unless you PMed them, I don't think they would have received any notifications and probably don't know of any posts after #7.

Having said that... I do like your signature line over at VBAExpress.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hyperlink to open another sheet in same workbook with filtered data	tarunbaweja	Excel Programming	1	03-20-2016 06:52 AM
Copy Filtered Data to new excel file s	theexpat	Excel Programming	0	02-18-2016 10:24 AM
Creating a Custom List that Updates when one of the data fields is filtered	anthrus	Excel	1	11-13-2014 11:58 PM
How to automatically sum in excel for filtered data	msi_g	Excel	2	08-05-2013 05:29 AM
Copying & Pasting to Firefox problems	mso2u	Word	2	04-07-2011 07:26 AM