Transfering large amounts of autofiltered data to new worksheet

Hello everyone, I have a problem with my current macro that uses a basic autofilter to auto filter from the parent database to extract the correct rows and then copies the query and pastes it into a new worksheet to further proceed with the macro.

I have run into a problem because my database has become very big and now when I try to autofilter the query and click on copy, an error regarding the data range reference is too complex - use data that can be selected in one contiguous rectangle

I tried a few things such as to autofilter out everything I dont need and hit delete - this does not work either, same result

I got help here previously in which the code deletes All Hidden Rows and this is very time consuming, I have not tested all my methods but it took 15 minutes to delete hidden files for one method and theres roughly 5 in total

I have to end up running this code on the parent worksheet multiple times because I use the parent worksheet to extract different parameters into different worksheets!

I have noticed that if I manually copy the data in smaller blocks, by halving the data seems to work,but I do not know how large of a partition I am limited to copy because my database is very large and the size varies month-to-month so I cannot put a number on the max range. I think if I could get a macro to do it by thirds or preferably quarter range should be safe.


So just to summarize, I am trying to devise a method in which I would auto filter on the active parent sheet "sheet 1" and I would copy the auto-filtered query to "sheet2" instead of copying the whole worksheet in one instance I would like to split the autofitlered query into four equal parts with respect to the range of the worksheet and then to copy the first quarter of the query and paste in sheet 2 and then the second quarter to sheet 2 and so on untill all four quarters are done one after the other, so sheet 2 should be a series of all four parts combined into one series on sheet2



Any help is appreciated!

Originally Posted by opg

Hi, I was unsuccessful at trying this because I was unable to make it work using Advanced FIlter, for example,


How would I filter out Alpha,Alphabet (previously used equals Alpha* statement using Autofilter) out of column F and transfer to say, sheet7 ?

Hi

Is the parent database contained within an Excel file? I would potentially consider using a separate workbook to query the database using Data>Get External Data (or by using a code solution eg with ADO) as this won't suffer from the same limitations.

Note that I rarely use AdvancedFilter on large datasets simply because I find it incredibly slow when there is a lot of data (it raises my blood pressure waiting for it to run and significantly endagers the continued wellbeing of my computer!).

Richard

Hi, to answer your question, all the content is in one excel file, I actually retrieve the parent database in a separate excel file and copy-> paste it into the main worksheet in the workbook.I am all for the export feature if that helps!

Hi

To use advanced filter to copy to another sheet.

1) Sheet1 columns A:F has your data with headings in row 1.
2) Sheet7:A1:F1 have the same headings.
3) Sheet7:H1 has the same heading as F1
4) Sheet7:H2 has "Alpha*"
5) Highlight Sheet7!A1:F1 then
filter, advanced filter, go through the message about the headings, then fill out the 3 items List range (by going to sheet1), Criteria range (H1:H2) and output range A1:F1 (both on the output sheet).

This should filter the data from sheet1 and output to sheet7

rylo

Hi rylo, I would like to inform you that I have data upto column I so instead of using column H as in your steps I used column K, I tried many different ways and the prompt I keep getting is,

This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following

-If you're using an advance filter, select a range of cells that contains atleast two rows of data. Then click the Advanced filter comand again

) Highlight Sheet7!A1:F1 then
filter, advanced filter, go through the message about the headings, then fill out the 3 items List range (by going to sheet1), Criteria range (H1:H1) and output range A1:F1.

This should filter the data from sheet1 and output to sheet7

Im trying to follow this step right here, I highlight A1:I1 on sheet 7 and go to Advanced Filter
-Select copy to another location
-List Range: I click in the text box and then I click on Sheet1 tab and select A1:I1
-Criteria Range: I click on the text box which takes me to Sheet7 and then I clik on the K1 cell

-Copy to: I click on the text box and then click on Sheet7 and proceed to select cells A1:I1


this does not seem to work

I thought Advanced Filter can only copy to the same sheet

Thanks for sharing Rylo, the manual method certainly seems to work.