Can VBA do what cannot be done with Advanced Filtering?

[K. Georgiadis]

I tried to solve the following problem with Advanced Filter but I'm facing two big obstacles: (1) the data list contains links to other worksheets, which Advanced Filtering cannot cope with and (2) the data list is dynamic with constant changes being made to it.

Here is my question:

I have a data list in A7:K198 (with row 7 containing the column headings). Each row represents a complete record with sales projections by product and by year. Column D contains a single letter codes (F, H, I or O) which, placed in front of every record, identify the product class.

In separate parts of the sheets containing the data list I want to show complete records that are coded F, H, or I, preferably linked dynamically to the data list so that the user always has access to the updated secord sort.

Is this possible with VBA?

[austenr]

Can you post a sample workbook?

[K. Georgiadis]

How, praytell?

[K. Georgiadis]

the workbook is large (well over 1 meg), but imagine the following column headings:
Active Ingredient, Product Class (F, H, I, or O), Product Name, 2005 Volume, 2005 Price, 2005 Sales Value (multiplication of 2005 Volume * 2005 Price), 2006 Volume, 2006 price, 2006 Sales Value (multiplication of 2006 Volume * 2005 Price). All cells, except those in column Product Class and the calculated columns of 2005 and 2006 Sales Value, get their data from other parts of the workbook.

[MWE]

the workbook is large (well over 1 meg), but imagine the following column headings:
Active Ingredient, Product Class (F, H, I, or O), Product Name, 2005 Volume, 2005 Price, 2005 Sales Value (multiplication of 2005 Volume * 2005 Price), 2006 Volume, 2006 price, 2006 Sales Value (multiplication of 2006 Volume * 2005 Price). All cells, except those in column Product Class and the calculated columns of 2005 and 2006 Sales Value, get their data from other parts of the workbook.

if you are on broadband, T1, DSL or any other highspeed service, 1 MB is nothing. Posting an example spreadsheet will help you get an answer more quickly and it is much more likely that the answer will work.

1. make a copy of the file
2. remove anything you would not want others to see from the copy
3. zip the copy
4. repost to this thread and click the Manage Attachments button in the Additional Options Section
5. follow the instructions

[K. Georgiadis]

I'll hopefully be able to do this this evening (unless the Yankees play a terrific game!)

[Bob Phillips]

Here is an example of what I think you mean.

[K. Georgiadis]

This looks like it does what I'm looking for. Would you mind telling me a little bit about how the code works, so that I can adapt it to my workbook that has several more columns. Specifically:

are the following fragments of code the ones directing the sorted data to be "dumped" in tabs F,H.I and O:?

FilterAndCopy rng, "F"
FilterAndCopy rng, "H"
FilterAndCopy rng, "O"
FilterAndCopy rng, "I"

also, which line(s) of code set the filters as F, H, O, and I, directing the records meeting the criteria to b "dumped" on the corresponding worksheets?

[Bob Phillips]

This looks like it does what I'm looking for. Would you mind telling me a little bit about how the code works, so that I can adapt it to my workbook that has several more columns. Specifically:

are the following fragments of code the ones directing the sorted data to be "dumped" in tabs F,H.I and O:?

FilterAndCopy rng, "F"
FilterAndCopy rng, "H"
FilterAndCopy rng, "O"
FilterAndCopy rng, "I"

also, which line(s) of code set the filters as F, H, O, and I, directing the records meeting the criteria to b "dumped" on the corresponding worksheets?

The code is very simple. rng is setup to the size of the data, dynamically, and then it is filtered 4 times, in FilterAndCopy, which is passed the range and the filter criteria arguments. FilterAndCopy does the filtering and copy and paste.

[sheeeng]

I tried to solve the following problem with Advanced Filter but I'm facing two big obstacles: (1) the data list contains links to other worksheets, which Advanced Filtering cannot cope with and (2) the data list is dynamic with constant changes being made to it.

Here is my question:

I have a data list in A7:K198 (with row 7 containing the column headings). Each row represents a complete record with sales projections by product and by year. Column D contains a single letter codes (F, H, I or O) which, placed in front of every record, identify the product class.

In separate parts of the sheets containing the data list I want to show complete records that are coded F, H, or I, preferably linked dynamically to the data list so that the user always has access to the updated secord sort.

Is this possible with VBA?

I wonder bout that too. Is it possible?

[K. Georgiadis]

I was able to act on the previous suggestion of providing a sample file.

I have extracted a representative data set from my workbook to illustrate my task. I want to perform the sort on the sheet named "Summary" which is linked directly to "Raw Data" and therefore contains identical data. The reason why I want to do this is because I want to make sure that the code can handle data links to other worksheets.

F=farming, H=Hardwoods I=Industrial and O=Others. I created blank worksheets with these full names and would like the data to be sorted by F, H, I and O and to be "dumped" as complete records to the corresponding worksheets.

[Bob Phillips]

Triggered by any change on the Summary sheet

[K. Georgiadis]

Outstanding! Thank you very much for your help. I'm marking this "solved"

[K. Georgiadis]

XLD, sorry to be a pain but in testing this for all eventualities I noticed the following:

when the workbook is first opened, the sorting apparently does take place and functions correctly. However, the process does not appear to repeat itself each time I reopen the workbook. For example, when (a) I clear the contents in the tabs "Farming," "Hardwood," "Industrial," and "Others," (b) save the workbook, and (c) reopen it, it does not repopulate the cleared worksheets. How can I fix that?

Thanks for your help and your patience