PDA

View Full Version : [SOLVED] Can VBA do what cannot be done with Advanced Filtering?



K. Georgiadis
09-22-2005, 11:30 AM
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
09-22-2005, 11:50 AM
Can you post a sample workbook?

K. Georgiadis
09-22-2005, 11:55 AM
How, praytell? :)

K. Georgiadis
09-22-2005, 12:05 PM
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
09-22-2005, 01:39 PM
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.


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

K. Georgiadis
09-22-2005, 03:56 PM
I'll hopefully be able to do this this evening (unless the Yankees play a terrific game!)

Bob Phillips
09-22-2005, 03:58 PM
Here is an example of what I think you mean.

K. Georgiadis
09-22-2005, 05:23 PM
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
09-23-2005, 12:38 AM
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
09-23-2005, 02:43 AM
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
09-23-2005, 08:33 AM
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
09-23-2005, 08:55 AM
Triggered by any change on the Summary sheet

K. Georgiadis
09-23-2005, 09:19 AM
Outstanding! Thank you very much for your help. I'm marking this "solved"

K. Georgiadis
09-23-2005, 11:16 AM
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