Copying filtered data

**Lvenom** · 04-24-2010, 12:22 PM

I have a spreadsheet with mutiple columns that I am filtering on the "Assign" field. I can write the macros for the filtering but am stuck with copying the data. My problem, I only want to copy the data in columns A, B and C for all visible rows and paste it onto the corresponding sheet with the Assign name in cell A20. I don't need any of the data from columns D through to O and I cannot delete those columns from the main sheet. Once the data is copied over to the other sheet, I want to delete the filtered data from the main sheet (SCHEDULE) so I don't have to see it again. Based on the sample I am providing, need to filter for all Assign = Sam B, copy data to tab Sam B, delete data once copied over. I have searched but could not find anything specific to what I need. Any help would be appreciated. Working in Excel 2003

**shg** · 04-24-2010, 01:16 PM

Maybe like this:

Please Login or Register  to view this content.

**JBeaucaire** · 04-24-2010, 01:46 PM

My take on it...I assume you are wanting to take the data from SCHEDULE that matches each worksheet and put it in A20 of the appropriate sheet by employee name. Then delete the rows from the SCHEDULE after they are transferred.

This will do that:

Please Login or Register  to view this content.

**Lvenom** · 04-24-2010, 03:33 PM

I have tried both examples provided by shg and JBeaucaire and both work better than expected. Thank you for the timely and helpful response (to both of you). This site has always been a useful tool to build and develop one's understanding and control of excel.

**Lvenom** · 04-29-2010, 12:20 PM

Initially this was solved successfully and I was satisfied with the results. I have had a request for modifications to this and rather than re-write the wholoe thread I figured I would just add to this one. The filtering works fine, what I need it to do now is find the next available row if cell A20 happens to already have data in it (on the individually named sheets. Just trying to avoid the mess if someone happens to rerun the macro a second time and overwrites what has already been pasted there. Any help here would be appreciated

**JBeaucaire** · 04-29-2010, 01:24 PM

I would suggest this, each time the macro is run it will find the next empty row of data by looking at column A, then put the data there.

Please Login or Register  to view this content.

**Lvenom** · 04-29-2010, 01:58 PM

That's close. I still need it to start in cell A20 though. Looking at column A is fine but my only problem is that cells A1 through to A19 don't always have data in them. The method you provided starts looking at cell A1 and then pastes in the first available cell which might be A5. I need it to start at A20. Hope that helps. By the way, thank you for the quick response

**JBeaucaire** · 04-29-2010, 02:23 PM

Please Login or Register  to view this content.

**Lvenom** · 04-29-2010, 03:22 PM

Thank you for the update and response JBeaucaire. Seems like the new macro wants to paste all the way down at line 460, then for the next tab it pastes from wherever the last tab left off (i.e. if the first paste was from line 460 to line 478, next tab goes from line 479 on.) It does this for each tab incrementing from the previous tab. Not to worry though, I added some "dummy data" to the first few cells on the spreadsheet and your first response to my dilemma is working fine. I'll stick with that. Thank you for the help.