[excel's most difficult problem] how to paste into a filtered column in excel 2007?

[unpronounciable]

Hi everyone!

This is my first question I've posted here.

Sorry if I included the 'Excel's most difficult problem' in the title, but I really do think it is. I've googled the solution for 4 hours and found no working solution yet.

I have two different files. They are incidents database. File A is on All Incidents, and File B is Spills Only.

So in File A, I filtered out 'Spill' incidents and now I have the exact same list as File B.

Now, I want to copy a column from File B and paste it to File A (remember, File A is now filtered). The pasted columns will populate the hidden cells too.

How can I paste on visible cells only?

Here I've attached these two files for your attempt. The column I want to copy from File B and paste it to File A is 'Time of spill'.

https://docs.google.com/viewer?a=v&...jUtOTdjZS00MzFkLTgxNjEtOTg1ODkzOGU5MGI3&hl=en
https://docs.google.com/viewer?a=v&...DEtMmRkYS00MGIwLTk4MjgtODZiMjU0ZTFkN2E2&hl=en
(Click on the link, and then click 'File > Save Original' after the file has been loaded.)

.

These are some solutions that I've found while googling, but none of them really helps.

1. Manually copy-and-paste each cell. (Not very helpful, since my actual database has thousands of incidents).

2. Sort the incidents in File A to match File B. (Yes, this might be the closest to what I want, but I'm still frustrated I cannot get the solution.)

3. Delete the unwanted incidents. In my example, delete all non-spill incidents. (I still want the other incidents.)

4. Filter File A to show only Spills incidents, then highlight the column I want to paste, then Alt+; which is 'select visible cells only', then only paste. (It won't work. You can try. Excel 2007 won't allow pasting on non-continuous cells.)

5. Use formula. Example, =C1, then I drag down. (It can work if the cells are from the same worksheet and on the same rows, but in my case, I won't get the desired results.)

6. Some macros that won't work.

.

Oh and if you might ask me, "Why do you need 'Time of spill'? Why don't you make 'Time of incident', which would make it relevant for all rows?" Well, actually, there are a lot of other data, but I'm trying to protect the confidentiality.

Thanks :wink:!

 

[snb_]

with sheets(1).usedrange.columns(1)
   .autofilter 1, "spills"
   .offset(1).copy sheets(2).cells(rows.count,1).end(xlup).offset(1)
   .autofilter
end with

 

[jkpieterse]

You're fighting an Excel limitation I'm afraid.
You cannot paste on filtered cells, unless the cells are contiguous (no hidden/filtered rows in between). So the only way to achieve this by pasting is by FIRST sorting the table on the column you are filtering on, then do the filter and finally, do the copy/paste.
And of course the order of the sorted and filtered rows must be the same as the order of the source you copied.

 

[unpronounciable]

with sheets(1).usedrange.columns(1)
   .autofilter 1, "spills"
   .offset(1).copy sheets(2).cells(rows.count,1).end(xlup).offset(1)
   .autofilter
end with

hi
thanks for the code
im very noob in macros
may i ask, if i were to use this macro for other columns/sheets/workbook, which one should i change?
i notice the "spills" part, does that refer to the sheet name or column name?
thx!

 

[tobi_55]

There is one simple way to do it if you have a reference column for the use of vlookup (line numbers, for example). Excel 2007 seems to paste a block of consecutive cells into hidden cells of a filter but not formulae.

1) Write a vlookup formula in the first cell of the filtered liset that fetches the content from the respective block of cells in the unfiltered list.
2) Copy the vlookup formula into all visible cells in the filtered sheet. You can do this on one go, from top to bottom, without overwriting the hidden cells.
3) Copy and paste the whole column of the filtered list as values to get rid of the formulae.

Done.