(VBA) Copy Parts of One Table (List) to New Table (List)

[Filibuster]

Hi All,

I have a large table (8000 rows) - I need to identify certain rows based on a criteria, and copy parts of the identified row (e.g. not all cells in the identified row) to a different table on another worksheet.

By iterating through each row in the table, I essentially have a solution that works - but it is terribly slow. I would like to instead create a solution that relies on AutoFilter to do the bulk of the work to identify which rows to copy - which will normally number somewhere in the 10-20 rows of the original 8000.

The attached workbook shows the example. I need to copy the data from RawData (tblRaw) tab to FilterData (tblFilter) tab. You can assume that the AutoFilter is already set up (in this case Head3 set to 'Female'). I need to copy the visible rows minus the Head2 and Head4 cols from the resulting table to the new table. In the actual spreadsheet I am using (not the example here), I will also need to manipulate some of the data before copying.

Note also, my method for creating the new table is to use VBA to delete each individual row in any existing tblFilter, and then add new rows as needed as I move the data from tblRaw. If there is a better way (e.g. faster than walking through the old table and deleting each row), I'd be interesting in understanding that solution also. Thanks

TableCopy.xlsx

[rylo]

Hi

Rather than autofilter, how about using the advanced filter. The code below puts in a heading and criteria, but you could have them set up.

Please Login or Register  to view this content.

rylo

[nilem]

or something like this

Please Login or Register  to view this content.

[Filibuster]

Thanks for the good ideas - I have not used advanced filter but see the potential... So, here is a bit of a rub... As I mentioned, I need to manipulate data prior to copy. In reality, what I really need to do is as follows:

The actual data has three additional columns. I need to perform the copy only if at least one of the additional columns contains a non-zero numeric value (and assuming the 'Female' criteria in my example is also met). How could I integrate this feature into the above solutions?

[rylo]

Hi

Update your example file so that it fully covers your situation, and show exactly what result should come from the sample data, and explain why.

rylo

[Filibuster]

Hi Rylo - I got pulled into a project at work, and have not had time to update the example. I will do so shortly. Thanks for your patience.