Copy entire row of data into new workbook

Hello
I would like some assistance with a data organising problem. I have a large spreadsheet with 7 columns, and up to about 50 000 rows. I need a macro that will copy the entire row of data and paste the data based on whether the value in the sixth column (Column F) matches a specified range of values. For example, there might be 200 possible different values in column F, but I want to specify certain values (perhaps 50 different values), that if they appear in column F, then the entire row is copied and pasted in the same spreadsheet.

I have attached an example spreadsheet, so in this you can see an example of the data format in the first tab, and assuming that the Col F values that we want to keep are A,F,V (in the actual data that I am working with there will be more like 50 different col F values that I want to retain), you can see the refined output in Column K.

Finally, the data source that I will be using is a .csv file, I'm not sure if this will be an issue at all?

Any assistance here would be greatly appreciated.

Regards
T85K

Personally I would use auto filter to extract the data I need with a setup along these lines.

First sheet is the “Data” sheet, then an “Info” sheet that contains all the relevant values i.e. A, F, E and so fort. The last sheet would be the “Result”

Marco starts by naming range in “Info” sheet, then sets an auto filter in sheets “Data” and loops through all values in “Info” sheet (A, F, E …) and copies the filtered range to “ Result” sheet.

Then the “Result” sheet could be saved as a stand alone file.

The only ting I see differ from you shown result is that you have the copied rows intermixed i.e. A, F, A, E, F … as opposed to an auto filtered result where you will get
A, A, A, E, E, F, F, F

Advantage by using auto filter is that it takes much less time filter the data than looping through 50 000 lines checking each and every one.

Alf

Thank you for your response Alf. It seems like a very good solution to the problem. However, I am not experienced in writing code, could you please provide me with the code to execute this operation if possible?
Many thanks
T85K

please provide me with the code to execute this operation if possible

No problem. I've build a model for you with my suggested layout. To test you first update "filter criteria" in sheet "Info" i.e. values in B3 to B?? and then run macro "Filter".

Macro starts by clearing sheet "Result", it then sets the range for "unique" i.e. sheets "Info" range B3 to B??

Then sheets "Data" is activated and auto filter is set on range A1 to G1 and cell values from "unique" are used for criteria and filtered results are copied to sheet "Result".

If this model seems useful to you I think I would like a small sample of your csv file where you take the data from. Importing data from a specific csv file could then be added to macro.

You talk about up to 50 criteria’s then perhaps it should be a checking function of the values of the "Info" sheet to avoid duplicate values being entered?

At the moment the "Result" sheets values will be presented in the same order as the B3 to B?? on the "Info" sheet. One could sort the "Info" sheet values (A-Z or Z-A) prior to filtering as this will give you the filtered results in the same order.

Finally, yes it's possible to get the result in the order they appear on the data sheet. By adding an extra column containing the row numbers of every row of data the final result in "Result" sheet could then be sorted from the lowest to highest and then this "helper" column will be deleted.

Alf

Thank you Alf, that macro works perfectly. I really appreciate your help.

Thanks for feedback and rep!

Alf